Triggers in SQL Server
In SQL server there are different types of triggers are present
- DML triggers
- DDL triggers
- Logon 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
- After triggers / For triggers
- Instead of triggers
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.
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;
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','[email protected]','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
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;