Create a Trigger in SQL SERVER

           

       In this article we are going to see how to create a trigger in table while inserting and delete a records, let we take a scenario . First create a employee table to maintain the employee records and another table to count the number of employee in the table.

whenever an insert operation is done in employee table we are increment the empcount table ecount column 1 and for deletion we are decrement one from that column.To do this we are  creating a trigger for a table while inserting and updating.

Trigger have magic tables called Inserted and Deleted, I.e while whenever inserting a record in employee table that records also have an entry in Inserted magic table.

Using this i am going to find the while this is insertion or deletion operation

Query:

/* CREATE A TABLE */

CREATE TABLE EMPCOUNT(ECOUNT INT)

INSERT INTO EMPCOUNT(ECOUNT) VALUES(0)

/* CREATE A TRRIGER FOR INSERT AND DELETE OPERATION */

CREATE TRIGGER trig_insert ON EMPLOYEE

AFTER INSERT ,DELETE

AS

BEGIN

SET NOCOUNT ON

DECLARE @ISINSERT BIT

      SELECT @ISINSERT = 1 FROM INSERTED

      IF(@ISINSERT = 1)

      BEGIN

            UPDATE      EMPCOUNT

            SET         ECOUNT = ISNULL(ECOUNT,0) + 1

      END

      ELSE

      BEGIN

            UPDATE EMPCOUNT

            SET    ECOUNT = ISNULL(ECOUNT,0) -1

      END

SET NOCOUNT OFF

END

/* RECORDS PRESENT INTIALLY */

SELECT * FROM EMPLOYEE

SELECT * FROM EMPCOUNT

/* INSERT THE RECORDS IN EMPLOYEE TABLE */

INSERT INTO EMPLOYEE(NAME,AGE) VALUES('HANY',25)

SELECT * FROM EMPCOUNT

INSERT INTO EMPLOYEE(NAME,AGE) VALUES('RAJ',16)

SELECT * FROM EMPCOUNT

SELECT * FROM EMPLOYEE

Output :

In the above output, you can see when the first time there is no records in the employee table and employee count table have 0 value. After a consecutive two insert records empcount automatically increment as 2.


From this article you can learn how to create a trigger for a table.