What Is Triggers In SQL By Sagar Jaybhay Part 1

In this series of article you will understand What is Triggers in SQL Server and How many Different Types of Triggers Present in SQL Server by Sagar Jaybhay.

Triggers in SQL Server

In SQL
server there are different types of triggers are present

1. DML triggers
2. DDL triggers
3. Logon triggers

DML Triggers

This type
of trigger is fired automatically when DML query is executed on an underlying
table means Insert, Update or Delete query fired then DML triggers are
triggered.

DML stands for data manipulation trigger and it is fire when data is modified using this insert, update or delete query or command.

DML triggers are classified into 2 different types of triggers

1. After triggers / For triggers
2. Instead of triggers

After Trigger

The name itself suggests that when the query is executed after that after trigger will fire. This means that after triggering action the after trigger will fire. It means after complete execution of Insert, Update and Delete query the trigger will fire.

Instead Of Trigger

This instead of trigger will fire, instead of triggering action. This Insert, update and delete are causes to fire instead of trigger.

In
general, we can consider a trigger a stored procedure or function which can
trigger after some kind of triggering action.

When you create a trigger you can create this for a specific table and specific event.

After Trigger

This
trigger will fire after the operation is completed means insert, update or
delete.

From this
trigger, we get inserted row in which whatever the value we inserted table we
get this. Inserted is a table which is also called a Magic table which is
maintained by the SQL server and which retains a copy of row which we inserted
into the table. It is accessed inside the context of creating a trigger

We are having simple student table in that 5 rows and we are creating after triggering on insert by which trigger will fire when we insert a row and whatever value inserted we get this simply by using select * from inserted; query in the trigger. Below is a query for that trigger

create trigger afterinsert
on studenttable
for insert
as
begin
select * from inserted
end;

AfterInsert Trigger

In this
above image, you can see when insert command completed successfully the select
will fire.

Now we have to add newly added row into another table how we do that

alter trigger afterinsert
on studenttable
for insert
as
begin
declare @id int;
declare @fullname nvarchar(2000);
select @id=Id from inserted;
select @fullname= first_name from inserted;

insert into afterinserttable values(@id,'name is '+CAST( @fullname as nvarchar(100)) + cast(GETDATE() as nvarchar));
select * from afterinserttable;
end;

insert into studentTable values(7,'sagar1','jaybhay1','sagar1@sagarjaybhay.net','Male','2020-02-07');

In the above query, we inserted data into the newly created table which is afterinserttable and we select all rows from that table. When the trigger is called row is inserted and all rows from that table are selected. See below image

Alter afterinsert trigger

In insert trigger, we get Inserted table in a trigger like that for delete trigger we get deleted table in that create a table. If we try to access this outside create trigger syntax we get an error.

create trigger afterdelted
on studenttable
for delete
as
begin
declare @id int;
declare @fullname nvarchar(2000);
select @id=Id from deleted;
select @fullname= first_name from deleted;

insert into afterinserttable values(@id,'name is '+CAST( @fullname as nvarchar(100)) + cast(GETDATE() as nvarchar));
select * from afterinserttable;
end;

GitHub: https://github.com/Sagar-Jaybhay