Tuesday, 5 November 2013

TRIGGER - 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.

No comments:

Post a Comment