Tuesday, 5 November 2013

UPDATE TRIGGER IN SQL SERVER

In this article we are going to see how to create update trigger ? what is the update trigger , the trigger which is fired when a updation happens in table. now here in trigger we can filter the updation process based on the columns. i.e when updating a particular column i have to do some updation in another table.

Let take a scenario whenever a updation happens in salary column of a employee table i have to update the grade of the employee in performance table. if any other updation in other than salary column i dont want ot process. so Update("Column name") is the filter to do this.


CREATE TABLE EMP_PERFORMANCE_TABLE
(
      ID INT,
      SALARY INT,
      GRADE CHAR(1)
)

/* create  a update trigger */
CREATE TRIGGER TRIG_UPD ON EMPLOYEE
AFTER UPDATE
AS
BEGIN

DECLARE @ID INT
DECLARE @SALARY INT
DECLARE @GRADE CHAR(1)

      IF(UPDATE(SALARY))
      BEGIN
                  SELECT @ID = ID, @SALARY = SALARY FROM INSERTED
                 
                                    SELECT @GRADE =   CASE
                                                WHEN @SALARY > 25000 THEN 'D'
                                                WHEN @SALARY > 30000  THEN 'C'
                                                WHEN @SALARY > 65000 THEN 'B'
                                                WHEN @SALARY > 150000 THEN 'A'
                                                ELSE 'E' END
                                               
                  IF EXISTS(SELECT '1' FROM EMP_PERFORMANCE_TABLE WHERE ID = @ID)
                  BEGIN
                        UPDATE EMP_PERFORMANCE_TABLE
                        SET    SALARY     =     @SALARY,
                             GRADE  =     @GRADE
                      WHERE ID = @ID
                  END
                  ELSE
                  BEGIN
                        INSERT INTO EMP_PERFORMANCE_TABLE(ID,SALARY,GRADE)
                        SELECT @ID,@SALARY,@GRADE
                  END
      END

END

SELECT * FROM EMPLOYEE
SELECT * FROM EMP_PERFORMANCE_TABLE

UPDATE EMPLOYEE
SET AGE = 24
WHERE ID = 10

SELECT * FROM EMP_PERFORMANCE_TABLE

UPDATE EMPLOYEE
SET SALARY = 80000
WHERE ID = 10

SELECT * FROM  EMPLOYEE
SELECT * FROM EMP_PERFORMANCE_TABLE






From this article you can learn , how to do update trigger against a particluar column  in a table.

No comments:

Post a Comment