What are Triggers in MSSqlserver

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database.
The following are major features of database triggers and their effects:

* triggers do not accept parameters or arguments (but may store affected-data in temporary tables)
* triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
* triggers can cancel a requested operation
* triggers can cause mutating table errors

Triggers are of 3 types in SQL Server 2005:
1. DML Triggers
. AFTER Triggers
. INSTEAD OF Triggers
2. DDL Triggers
3. CLR Triggers
Note:DDL and CLR Triggers cannot work in SQL Server 2000
DML Trigger:-These Trigger is fired only when INSERT, UPDATE, and DELETE Statement occurs in table.
Explanation on DML Trigger:
Let us create a Table and insert some records in that Table.
1) After Triggers:
After Triggers can be created in 3 ways.
1) After INSERT
2) After UPDATE
3) After DELETE

1) creating After INSERT Trigger:-

Syntax:
create trigger triggername
on tablename
AFTER INSERT
As
[SQL Statement/PRINT command]
GO

Eg:
create trigger afterinsert_trigger
on emp
AFTER INSERT
as
PRINT ‘AFTER TRIGGER EXECUTED SUCESSFULLY’
GO

Creating AFTER UPDATE TRIGGER:-
create trigger afterupdate_trigger

on emp

AFTER UPDATE

as

PRINT ‘AFTER UPDATE TRIGGER EXECUTED SUCESSFULLY’

GO

Creating AFTER DELETE TRIGGER:

Create trigger afterdelete_trigger

On emp

AFTER DELETE

as

PRINT ‘AFTER DELETE TRIGGER EXECUTED SUCESSFULLY’
GO
Instead Of Update Trigger

Creating INSTEAD OF UPDATE TRIGGER:-

create trigger insteadofupdate_trigger
on emp
INSTEAD OF UPDATE
as
PRINT ‘INSTEAD OF UPDATE TRIGGER EXECUTED SUCESSFULLY’
GO
Instead of Delete Trigger

Creating INSTEAD OF DELETE TRIGGER:-

create trigger insteadofdelete_trigger
on emp
INSTEAD OF DELETE
as
PRINT ‘INSTEAD OF DELETE TRIGGER EXECUTED SUCESSFULLY’
GO

For practice run given below queries
Create database dbTester

USe dbTester

CREATE TABLE tbl_Employee(IntId Int Not Null Identity Primary Key,
strEmpname varchar(20) Not Null Default ‘Unkown’,
strEmpPhone varchar(10) Not Null Default ’00-000-000′
)
SELECT *FROM tbl_Employee

SELECT *FROM tbl_Employee

select *from sys.objects where type_desc=’USER_Table’

INSERT INTO tbl_Employee(strEmpname,strEmpPhone)VALUES (‘abc’,’9988745′)

alter trigger afterinsert_trigger
on tbl_Employee
AFTER INSERT
as
UPDATE dbo.tbl_employee set strempname=’Ritesh’
WHERE dbo.tbl_employee.intId=(SELECT max(dbo.tbl_employee.intId) from dbo.tbl_employee)
PRINT ‘AFTER TRIGGER EXECUTED SUCESSFULLY’
GO

alter trigger afterupdate_trigger
on tbl_Employee
AFTER UPDATE
as
/*UPDATE dbo.tbl_employee set strempname=’Ritesh’
WHERE dbo.tbl_employee.intId=(SELECT max(dbo.tbl_employee.intId) from dbo.tbl_employee)*/
PRINT ‘AFTER UPDATE TRIGGER EXECUTED SUCESSFULLY’
GO

CREATE TRIGGER TempTrigger
ON tbl_Employee
INSTEAD OF INSERT
AS
BEGIN
PRINT ‘Insert trigger fired. Cannot insert values in the table TempTable.’
END

–after execute this command run insert command you will get new puzzle answer
–and also remember to insert recor into renamed table
sp_rename ‘tbemp’ ,’tbl_employee’

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s