SQL Server Trigger

Trigger is a special type of stored procedure ,which automatically executes with an event occurs in the database . A Triggers execute automatically when modifying data in a database table through  data manipulation language (DML) .Trigger fires when  INSERT, UPDATE,  DELETE is executed  on a table or view.

Here , we have created two table and inserted data to a table for example purpose.

CREATE TABLE student

(

             id INT NOT NULL,

             name VARCHAR (500) NOT NULL,

             age INT NOT NULL,

             adress VARCHAR (500) NOT NULL,

             date_of_birth DATETIME NOT NULL,

             height DECIMAL(10,2) NOT NULL

)

CREATE TABLE student2

(

            id INT NOT NULL,

             name VARCHAR (500) NOT NULL,

             age INT NOT NULL,

             adress VARCHAR (500) NOT NULL,

            date_of_birth DATETIME NOT NULL,

            height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',14,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',15,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',16,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',17,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',18,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',19,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',20,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',21,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',22,'44,student street','11/08/2018',16

There are two kind of trigger  

1)After trigger (AFTER CLAUSE) 

1)Instead of trigger (INSTEAD OF CLAUSE) 

After trigger (AFTER CLAUSE)
The After trigger  fires after SQL Server completed  the DML execution 

Syntax

CREATE TRIGGER Trigger_Name

ON TBALE_NAME

AFTER DML(INSERT /UPDATE/DELETE)

AS

BEGIN

/* Statment*/

END

Example

CREATE TRIGGER INSERTT

    ON STUDENT

    AFTER INSERT

    AS

    BEGIN

           INSERT INTO student2 (id,name,age,adress,date_of_birth,height)

              SELECT id,name,age,adress,date_of_birth,height FROM student

    END

We have two table , student table 12 row and student2 have no row.Now we have created the trigger.

The trigger will fire, when a data will be inserted to student table.We have written the script to insert data to student table.

INSERT INTO student (id,name,age,adress,date_of_birth,height)

SELECT 5,'john25',22,'44,School Steet',GETDATE(),15

Now execute the statement.We will see the following output.

(12 row(s) affected)

(1 row(s) affected)

Now we query on student2.

 

SELECT * FROM student2

Output:

id  name   age  adress        date_of_birth        height 

1    John1    12    34,student street    2018-01-08 00:00:00.000    10.50
1    John10   21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11   22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00

Instead of Trigger (INSTEAD OF CLAUSE)
The Instead of Trigger fires before SQL Server starts the execution.

Syntax  

CREATE TRIGGER Trigger_Name

ON TBALE_NAME

INSTEAD OF DML(INSERT /UPDATE/DELETE)

AS

BEGIN

/* Statment*/

END

Example

CREATE TRIGGER INSERTT

ON STUDENT

INSTEAD OF INSERT

AS

BEGIN

        INSERT INTO student2 (id,name,age,adress,date_of_birth,height)

        SELECT id,name,age,adress,date_of_birth,height FROM student

END

We have deleted student2 table data.Now we have created the above trigger.Now we have write the following insert statement.

INSERT INTO student (id,name,age,adress,date_of_birth,height)

SELECT 5,'john25',22,'44,School Steet',GETDATE(),15

Now execute the statement.We will see the following output.

 

12 row(s) affected)

(1 row(s) affected)

Now we query on student2.

 

SELECT * FROM student2

Output:

id  name   age  adress        date_of_birth        height 

1    John1    12    34,student street    2018-01-08 00:00:00.000    10.50
1    John10   21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11   22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00 

You ca write trigger on all three DML statement (INSERT/UPDATE/DELETE). Here is example of DELETE statement trigger.

Eaxmple 1 (AFTER CLAUSE)

CREATE TRIGGER INSERTT

ON STUDENT

   AFTER DELETE

AS

BEGIN

         INSERT INTO student2 (id,name,age,adress,date_of_birth,height)

         SELECT id,name,age,adress,date_of_birth,height FROM student

END

Execute the delete statement.

DELETE FROM student WHERE age=12

Table data output

SELECT * FROM student2

id  name   age  adress        date_of_birth        height  

1    John10    21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11    22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00 

 

Eaxmple 2 (INSTEAD OF CLAUSE)

 

CREATE TRIGGER INSERTT

ON STUDENT

   INSTEAD OF DELETE

AS

BEGIN

         INSERT INTO student2 (id,name,age,adress,date_of_birth,height)

         SELECT id,name,age,adress,date_of_birth,height FROM student

END

Execute the delete statement.

DELETE FROM student WHERE age=12

Table data output

SELECT * FROM student2

id  name   age  adress        date_of_birth        height  

1    John10    21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11    22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00