Create a 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.