INSTEAD OF UPDATE 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   ERRDETAILS

(

ERRNO BIGINT IDENTITY(1,1),

JOBNO BIGINT,

JOBNAME VARCHAR(250),

NOOFITEMS BIGINT,

QTY BIGINT

)

INSERT INTO ERRDETAILS VALUES (1000,'APRARTMENT3',1,110000)

 INSERT INTO ERRDETAILS VALUES (2000,'APRARTMENT3',2,120000)

 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)

Create  view

CREATE VIEW VW_ORDERS

AS

SELECT O.JobNo,E.JOBNAME,E.NOOFITEMS,E.QTY,O.Amount,O.Amount1 FROM Ordermaster O INNER JOIN ERRDETAILS E  ON O.JobNo = E.JOBNO

GO



CREATE TRIGGER UPDATETRG ON VW_ORDERS

 INSTEAD OF 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 VW_ORDERS  
SET 
JOBNAME =@JOBNAME     
WHERE 
JOBNO = @JOBNO

PRINT 'AFTER UPDATE TRIGGER HAS BEEN FIRED.'
GO




UPDATE VW_ORDERS SET JOBNAME ='GREAT PORTLAND APARTMENTS12' WHERE JobNo = 1000


SELECT * FROM SERVICEMASTER

SELECT * FROM ERRDETAILS