Trigger - After insert and delete example

Question

I have a requirement as a trigger should get fired when any row is inserted or deleted from table FAB which contains num as unique value. and depending upon that num value, another table should be update.

e.g.

FAB table

num code   trs
10  A2393  80
20  B3445  780

Reel table

reelnr  num  use flag
340345  10   500  1

when num 10 from FAB table gets deleted(or any new num gets inserted), the trigger should get fired and should check the reel table which contains that num value and give the reelnr.

How to proceed with this?

There is something called `Inserted` and `Deleted` tables in `SQL SERVER` which can be used to fetch the newly inserted or deleted records. `SQL Server` automatically creates and manages these tables. You can use these `temporary, memory-resident tables` to test the effects of certain data modifications and to set conditions for `DML trigger actions` — Pரதீப், Nov 10 '14 at 06:26

score 0 · Accepted Answer · edited May 23 '17 at 11:49

you can Use Inserted & Deleted Table in SQL

These two tables are special kind of table that are only available inside the scope of the triggers. If you tries to use these tables outside the scope of Triggers then you will get Error.

Inserted : These table is use to get track of any new records that are insert in the table. Suppose there are Six rows are inserted in your table then these table will consist of all the six rows that are inserted.

Deleted : These table is used to track all the Deleted record from your tables. Last delete rows will be tracked by these table.

For Insert :

CREATE TRIGGER TR_AUDIT_Insert
ON Reel_table
FOR INSERT
AS
BEGIN
       INSERT INTO Reel_table (reelnr, num, use, flag)
       SELECT 
           reelnr,
           num,
           use,
           flag          
       FROM 
           INSERTED
END

For Delete :

CREATE TRIGGER TR_AUDIT_Delete
ON Product
FOR DELETED
AS
BEGIN
       INSERT INTO Reel_table (reelnr, num, use, flag)
       SELECT 
           reelnr,
           num,
           use,
           flag          
       FROM 
           DELETED
END

Note : I don't know from where these three reelnr, use flag values you are getting So, Please modify this as per your need.

This is the format of the Triggers that normally we use. You also can do this by using single trigger also I dont know what is your exact requirement If you want to achieve by only single Trigger then you can refer this link : Refer

This helped a bit. I changed the line for insert and for deleted to After Insert, Delete. Thanks Anyways — omkar patade, Nov 10 '14 at 15:54

Trigger - After insert and delete example

1 Answers1