AFTER UPDATE Trigger Example

CREATE TABLE SERVICEMASTER

(

SERVICENO BIGINT IDENTITY(1,1),

JOBNO BIGINT,

JOBNAME VARCHAR(250),

AMOUNT DECIMAL(18,2)

)

DROP TABLE ERRDETAILS

CREATE TABLE   ERRDETAILS

(

ERRNO BIGINT IDENTITY(1,1),

JOBNO BIGINT,

JOBNAME VARCHAR(250),

NOOFITEMS BIGINT,

QTY BIGINT

)

 INSERT INTO ERRDETAILS VALUES (1000,'APRARTMENT1',3,110000)

 INSERT INTO ERRDETAILS VALUES (2000,'APRARTMENT2',3,130000)

 INSERT INTO ERRDETAILS VALUES (3000,'APRARTMENT3',3,160000)

 INSERT INTO ERRDETAILS VALUES (4000,'APRARTMENT4',4,170000)

 INSERT INTO ERRDETAILS VALUES (5000,'APRARTMENT5',5,180000)

 INSERT INTO ERRDETAILS VALUES (6000,'APRARTMENT6',6,190000)

 INSERT INTO ERRDETAILS VALUES (7000,'APRARTMENT7',7,200000)

 INSERT INTO ERRDETAILS VALUES (8000,'APRARTMENT8',8,210000)

 SELECT * FROM ERRDETAILS

 SELECT * FROM SERVICEMASTER

CREATE TRIGGER UPDATETR ON ERRDETAILS

 FOR UPDATE

 AS

DECLARE @JOBNO BIGINT;

DECLARE @JOBNAME VARCHAR(250);

DECLARE @AMOUNT DECIMAL(18,2);

SELECT @JOBNO = i.JobNo  FROM inserted I;

SELECT @JOBNAME = i.JobName  FROM inserted I;

SELECT @AMOUNT = i.NOOFITEMS * I.QTY  FROM inserted I;

IF UPDATE(JOBNAME)

PRINT 'RECORD UPDATION FOR ORDERNAME'

IF UPDATE(QTY)

PRINT 'QUANTITY HAS BEEN CHANGED'

UPDATE SERVICEMASTER SET JOBNAME = @JOBNAME WHERE JOBNO = @JOBNO

PRINT 'AFTER UPDATE TRIGGER HAS BEEN FIRED.'

GO

Execute the below line:

UPDATE ERRDETAILS SET JOBNAME ='GREAT PORTLAND APARTMENTS' WHERE JOBNO = 1000

SELECT * FROM ERRDETAILS - Record will be updated

SELECT * FROM SERVICEMASTER - Also record will be updated