AFTER DELETE Trigger Example

1) Create the below tables

CREATE TABLE SERVICEMASTER

(

SERVICENO BIGINT IDENTITY(1,1),

JOBNO BIGINT,

JOBNAME VARCHAR(250),

AMOUNT DECIMAL(18,2)

)

CREATE TABLE   ERRDETAILS

(

ERRNO BIGINT IDENTITY(1,1),

JOBNO BIGINT,

JOBNAME VARCHAR(250),

NOOFITEMS BIGINT,

QTY BIGINT

)

2) insert the data into table

INSERT INTO ERRDETAILS VALUES (1000,'APRARTMENT1',1,120000)

INSERT INTO ERRDETAILS VALUES (2000,'APRARTMENT2',2,140000)

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)

3)Create and Execute the below trigger

CREATE TRIGGER DELETETRG ON ERRDETAILS

 AFTER DELETE

 AS

DECLARE @JOBNO BIGINT;

SELECT @JOBNO = i.JobNo  FROM deleted I;

DELETE FROM SERVICEMASTER WHERE JOBNO = @JOBNO

PRINT 'AFTER DELETE TRIGGER HAS BEEN FIRED.'

GO

4) Execute the below statement

DELETE FROM ERRDETAILS WHERE JOBNO =1000

SELECT * FROM SERVICEMASTER

SELECT * FROM ERRDETAILS