Home > DeveloperSection > Articles > Trigger in SQL Server

Trigger in SQL Server


Database Database 
Ratings:
0 Comment(s)
 2296  View(s)
Rate this:

Trigger in SQL Server

In this article, I’m explaining the triggers in sql server and its types.

Triggers are pre-compiled SQL statements that are executed by the server whenever the user attempts to modify the data in the specified table. The user cannot explicitly execute triggers. SQL Server executes the trigger automatically immediately after the data modification statements are executed.

The behavior of a trigger is just like a transaction. A trigger fails whenever any part of the trigger is dissatisfied by the data supplied by the user. In such a case the data changes made by the user are not reflected in the specified table.

While defining a trigger, certain points have to be kept in mind:

·         Parameters cannot be passed with triggers.

·         Triggers names must be unique within a database.

·         Triggers cannot be created on views or temporary tables.

·         Triggers are used to maintain data integrity.

·         Triggers do not return any return sets to the user.

Syntax:

CREATE TRIGGER trigger_name

ON table_name

[FOR | AFTER | INSTEAD OF] {INSERT | UPDATE | DELETE}

AS statement

 

where

·         trigger_name : Name of the trigger.

·         Table_name : Name of the table on which trigger is created.

 

Example

CREATE TRIGGER t1

ON EMP

FOR INSERT

AS raiserror('%d rows have been modified', 0, 1, @@rowcount)

 

To see the effect of trigger, insert a new row to the table.

insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')

Output

1 rows have been modified

(1 row(s) affected)

 

Deletion of Triggers

The DROP TRIGGER statement can be used to delete triggers.

Syntax:

DROP TRIGGER trigger_name

DROP TRIGGER t1

 

Types of Triggers

Basically triggers are classified into two main types :-

1.       After Trigger

2.      Instead Of Trigger
<![if !supportLineBreakNewLine]>

After Trigger

These triggers run after an insert, update or delete on a table. 

After Triggers can be further classified into three types:

 

1.       After Insert Trigger

2.       After Update Trigger

3.       After Delete Trigger

 

After Insert Trigger

This trigger is fired after an INSERT on the table.

Example

CREATE TRIGGER Insert_Trigger

ON EMP

AFTER INSERT

AS PRINT 'INSERT TRIGGER EXECUTED AFTER INSERT QUERY'

 

After creating trigger, insert the row in the table and see the result

insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')

 

Output

INSERT TRIGGER EXECUTED AFTER INSERT QUERY

 

(1 row(s) affected)

 

After Update Trigger

This trigger is fired after an UPDATE on the table.

Example

CREATE TRIGGER Update_Trigger

ON EMP

AFTER UPDATE

AS PRINT 'UPDATE TRIGGER EXECUTED AFTER UPDATE QUERY'

 

After creating trigger, update the row in the table and see the result

update EMP set EMPFIRSTNAME='Smith', EMPLASTNAME='Johnson' where EMPID=5

Output

UPDATE TRIGGER EXECUTED AFTER UPDATE QUERY

 

(1 row(s) affected)

 

After Delete Trigger

This trigger is fired after a DELETE on the table.

Example

CREATE TRIGGER Delete_Trigger

ON EMP

AFTER DELETE

AS PRINT 'DELETE TRIGGER EXECUTED AFTER DELETE QUERY'

 

After creating trigger, delete the row in the table and see the result

delete from EMP where EMPID=5

Output

DELETE TRIGGER EXECUTED AFTER DELETE QUERY

 

(1 row(s) affected)

 

Instead Of Trigger

INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. Like AFTER triggers, each INSTEAD OF trigger gives you access to two virtual tables called Inserted and Deleted. For a DELETE trigger, the virtual table Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version.

Syntax:

CREATE TRIGGER trigger_name

ON table_name

AS

BEGIN

<SOL Statements>

END

 

Instead Of Trigger can be classified further into three types:-

1.       Instead Of Insert Trigger

2.       Instead Of Update Trigger

3.       Instead Of Delete Trigger

 

Instead Of Insert Trigger

 

Example

First create a table

CREATE TABLE EMP_TEST_AUDIT

(

EMPID int,

EMPFIRSTNAME varchar(50),

EMPLASTNAME varchar(50),

DEPT varchar(20),

SALARY int,

AUDIT_ACTION varchar(100),

AUDIT_TIME datetime

)

Create Instead Of Insert Trigger

CREATE TRIGGER INSTEADOF_TRIGGER

ON EMP

INSTEAD OF INSERT

AS

      declare @EMPID int;

      declare @EMPFIRSTNAME varchar(50);

      declare @EMPLASTNAME varchar(50);

      declare @DEPT varchar(20);

      declare @SALARY int;

     

      select @EMPID=i.EMPID from inserted i;

      select @EMPFIRSTNAME=i.EMPFIRSTNAME from inserted i;

      select @EMPLASTNAME=i.EMPLASTNAME from inserted i;

      select @DEPT=i.DEPT from inserted i;

      select @SALARY=i.SALARY from inserted i;

     

BEGIN

      insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY)

      insert into EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'INSERTED-INSTEAD OF TRIGGER',getdate());

      PRINT'RECORD INSERTED'

END

Insert a row in the table

insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')

Output

RECORD INSERTED

 

(1 row(s) affected)

 

Now you will see that record is also inserted in EMP_TEST_AUDIT table

Trigger in SQL Server

 

Inserted Of Delete Trigger

 

Example

CREATE TRIGGER INSTEADOF_DELETETRIGGER

ON EMP

INSTEAD OF DELETE

AS

      declare @EMPID int;

      declare @EMPFIRSTNAME varchar(50);

      declare @EMPLASTNAME varchar(50);

      declare @DEPT varchar(20);

      declare @SALARY int;

     

      select @EMPID=d.EMPID from deleted d;

      select @EMPFIRSTNAME=d.EMPFIRSTNAME from deleted d;

      select @EMPLASTNAME=d.EMPLASTNAME from deleted d;

      select @DEPT=d.DEPT from deleted d;

      select @SALARY=d.SALARY from deleted d;

     

BEGIN

      delete from EMP where EMPID=@EMPID

      insert into EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'DELETED-INSTEAD OF DELETE TRIGGER',getdate());

      PRINT'RECORD DELETED'

END

 

 

Delete a record from the table.

delete from Emp where EMPID=5

Output

RECORD DELETED

 

(1 row(s) affected)

 

Trigger in SQL Server

 

 

Instead of Update Trigger

 

Example

CREATE TRIGGER INSTEADOF_UPDATETRIGGER

ON EMP

INSTEAD OF UPDATE

AS

      declare @EMPID int;

      declare @EMPFIRSTNAME varchar(50);

      declare @EMPLASTNAME varchar(50);

      declare @DEPT varchar(20);

      declare @SALARY int;

     

      select @EMPID=i.EMPID from inserted i;

      select @EMPFIRSTNAME=i.EMPFIRSTNAME from inserted i;

      select @EMPLASTNAME=i.EMPLASTNAME from inserted i;

      select @DEPT=i.DEPT from inserted i;

      select @SALARY=i.SALARY from inserted i;

     

BEGIN

      update EMP set EMPFIRSTNAME=@EMPFIRSTNAME, EMPLASTNAME=@EMPLASTNAME where EMPID=@EMPID

      insert into EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'INSERTED-INSTEAD OF TRIGGER',getdate());

      PRINT'RECORD UPDATED'

END

 

Update the record in the table

 

update EMP set EMPFIRSTNAME='Smith',EMPLASTNAME='Johnson' where EMPID=4

 

Output

RECORD UPDATED

 

(1 row(s) affected)

Trigger in SQL Server


Don't want to miss updates? Please click the below button!

Follow MindStick