INSTEAD OF INSERT 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 trgInsteadOfINSERT ON  VW_ORDERS

INSTEAD OF INSERT 

AS

DECLARE @JOBNO BIGINT;

DECLARE @JOBNAME VARCHAR(250);

DECLARE @NOOFITEMS BIGINT;

DECLARE @QTY BIGINT;

SELECT @JOBNO=d.JOBNO FROM deleted d;

SELECT @JOBNAME=D.JOBNAME FROM deleted d;

SELECT @NOOFITEMS=d.NOOFITEMS FROM deleted d;

SELECT @QTY=d.QTY FROM deleted d;

BEGIN

if(@NOOFITEMS>2)

begin

RAISERROR('Cannot INSERT where NOOFITEMS > 2',16,1);

ROLLBACK;

end

else

begin

INSERT INTO ERRDETAILS(JOBNO,JOBNAME,NOOFITEMS,QTY)-- VALUES(@JOBNO,@JOBNAME,@NOOFITEMS)

SELECT JOBNO, JOBNAME, NOOFITEMS, QTY FROM inserted

COMMIT;

--insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

--values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());

   PRINT 'Record inserted -- Instead Of Insert Trigger.'

end

END

GO

INSERT INTO VW_ORDERS VALUES(1003,'APARTMENT 1003',1,100000,110000,120000)

SELECT * FROM ERRDETAILS

SELECT * FROM VW_ORDERS