AFTER INSERT Trigger Example

Create the below tables 

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
)


AFTER INSERT Trigger. 

CREATE TRIGGER ORDERS ON ERRDETAILS 
FOR INSERT
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;

INSERT INTO SERVICEMASTER(JOBNO,JOBNAME,AMOUNT)
VALUES  (@JOBNO,@JOBNAME,@AMOUNT)

PRINT 'AFTER INSERT TRIGGER  FIRED FIRED.'


GO

Add some data into tables :

 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