DML triggers(After and Instead of Triggers) and magic tables

Hello friends, today we are going to learn about DML triggers and magic tables. Let’s see quickly, what is a trigger and how do we use triggers.

Trigger Definition: - A trigger is a special type of stored procedure which executes automatically when a specific DML (Insert, Update, Delete) event is occurred for which the trigger was written. There are two types of triggers.

After Trigger: - After trigger is fired only after the successful completion of the triggering SQL statement.After trigger cannot be created on views. If after keyword is not specified while creating the trigger, SQL Server creates after trigger by default.

Instead of Trigger: - Instead of trigger may be defined as the trigger which is fired instead of the triggering statement that means, it overrides the action performed by the triggering statement.

I am sure that above statement doesn’t give clear picture about triggers and their types, but after implementation, you will be able to understand what I wanted to explain here.

Example of After Trigger:- To implement after trigger first of all we will create two tables "Emp" and "Sal_structure" and insert 3 rows in each table using below scripts.

Create table Emp(Eid int, Name varchar(100),Basic_Sal Int)

Go

Create table Sal_structure(Eid int,HRA int,Basic_Sal int)

Go

insert into Emp values(101,'Neeraj Yadav',10000)

insert into Emp values(102,'Rachit Sharma',5000)

insert into Emp values(103,'Akshay Sinha',8000)

Go

Insert into Sal_structure values(101,6000,10000)

Insert into Sal_structure values(102,3000,5000)

Insert into Sal_structure values(103,5000,8000)

Now we will write a trigger on table Emp using below script.

Create trigger trg_Emp on Emp after insert -->1

as

begin

      insert into Sal_structure(Eid,HRA,Basic_Sal)

      select Eid,Basic_Sal/100*40,Basic_Sal from inserted -->2

end

lets understand the above script, I have given sequence no’s to first line of the script and the second last line of the script. In whole script these two lines needs to be understood. 

In first line there are two keywords “after and insert” after indicates that it is an after trigger and “insert” indicates that this trigger will be executed if insert event occurred on "Emp" table.

In second last line, there is a keyword “inserted” which is also known as magic table. Magic tables are virtual tables which are used in the body of a trigger. If a user performs insert or delete operation on a table on which a trigger has been written, these(magic tables) stores the newly inserted values and also stores the old removed values of the table. Now let me tell you what is triggering statement (I have used this word multiple times above).

We have written after trigger for “insert” event using the above script, now we will see how it works and what is triggering statement for this trigger.

I am going to run below script and let’s see what happens.

Insert into Emp values(104,'Vikas',9000)

I have inserted one row into “Emp” table, but if you look at the result window, message “1 row affected” is showing two times. Second is coming from the trigger. Triggering statement is our insert statement as it triggered the after trigger written on “Emp” table. Lets see the “Sal_structure” table.

We can see that there is one new row added into "Sal_structure" table which has been inserted by the trigger automatically. I hope I was able to make you understand about the after trigger. Similarly we can write triggers for different DML events (Update and Delete). Below is the syntax for those.

For delete event:-

Create trigger trg_Emp on Emp after delete

as

begin

      Trigger body here

End

For update event:-

Create trigger trg_Emp on Emp after update

as

begin

      Trigger body here

End

For All events:-

Create trigger trg_Emp on Emp after insert,delete,update

as

begin

      Trigger body here

End

There are two types of magic tables.

Inserted: - This is also known as inserted buffer.

Deleted: - This is also known as deleted buffer.

Example of Instead of Trigger: - Instead of trigger can be defined on views or tables depending on the business logic. But instead of triggers are mainly used to update base tables of a non updatable view.

Let’s create a non updatable view first using below script.

Create view Vw_emp_Sal

as

select distinct E.Eid,Name,HRA,S.Basic_Sal

from Emp E inner join Sal_structure S on E.Eid =S.Eid

I have created a view that is non updatable as it contains “distinct” clause in it.

Now lets create a instead of trigger on this view using below script.

Create trigger trg_insteadof on Vw_emp_Sal instead of update

as

begin

      update Emp  set Basic_sal=inserted.basic,Name=inserted.Name

      from Emp inner join inserted on inserted.eid=Emp.eid

end

lets update the view using below SQL statement.

update vw_emp_sal set Basic=7000 where eid=102—> This is triggering statement.

As stated earlier, Instead of trigger overrides the action of triggering statement.

Our triggering statement is below.

update vw_emp_sal set Basic=7000 where eid=102—>This is triggering statement.

It means we are updating the Basic amount column of the view and that basic amount is coming from “Sal_sturcture”(S) table, refer below script.

Create view Vw_emp_Sal

as

select distinct E.Eid,Name,HRA,S.Basic_Sal

from Emp E inner join Sal_structure S on E.Eid =S.Eid

Lets See if “Sal_structure” table has been updated or not.

In the above image we can see that “Basic_Sal” column value has not been updated in table “Sal_Structure”. It means that instead of trigger overridden the triggering statement action. I hope I was able to make you understand.

I have taken only one example of each type of triggers, there can be many ways of usings those.Please let me know, if you have any queries.