INSTEAD OF DELETE Trigger Example

Create and insert the data into below tables 

CREATE  TABLE ORDERMASTER

(

JOBNO BIGINT,

JOBNAME VARCHAR(250),

AMOUNT BIGINT,

AMOUNT1 BIGINT

)

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

 INSERT INTO ERRDETAILS VALUES (2000,'APRARTMENT1',2000,120000)

 INSERT INTO ERRDETAILS VALUES (3000,'APRARTMENT1',3000,130000)

 INSERT INTO ERRDETAILS VALUES (4000,'APRARTMENT1',4000,140000)

 INSERT INTO ERRDETAILS VALUES (5000,'APRARTMENT1',5000,150000)

 INSERT INTO ERRDETAILS VALUES (6000,'APRARTMENT1',6000,160000)

 INSERT INTO ERRDETAILS VALUES (7000,'APRARTMENT1',7000,170000)

 INSERT INTO ERRDETAILS VALUES (8000,'APRARTMENT1',8000,180000)

 INSERT INTO ERRDETAILS VALUES (9000,'APRARTMENT1',9000,190000)

 INSERT INTO ERRDETAILS VALUES (1001,'APRARTMENT1',1001,110001)

CREATE TABLE SERVICEMASTER
(
SERVICENO BIGINT IDENTITY(1,1),
JOBNO BIGINT,
JOBNAME VARCHAR(250),
AMOUNT DECIMAL(18,2)

)

CREATE TRIGGER trgInsteadOfDelete ON  ERRDETAILS 
INSTEAD OF DELETE
AS
DECLARE @JOBNO BIGINT;
DECLARE @JOBNAME VARCHAR(250);
DECLARE @NOOFITEMS BIGINT;


select @JOBNO=d.JOBNO from deleted d;
select @JOBNAME=D.JOBNAME from deleted d;
select @NOOFITEMS=d.NOOFITEMS from deleted d;

BEGIN
if(@NOOFITEMS>=5)
begin
RAISERROR('Cannot delete where NOOFITEMS > 5',16,1);
ROLLBACK;
end
else
begin
delete from ERRDETAILS where JOBNO=@JOBNO;
COMMIT;

DELETE FROM SERVICEMASTER WHERE JOBNO = @JOBNO
   PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END

GO