Question
What are the DML triggers?
Answer
DML triggers are the triggers which are executed in response of a DML Statement like Delete, Insert, and Update. These triggers are against a table or a view. These triggers are helpful in maintaining the database integrity and for the audit purpose.
Types of DML trigger
In SQL Server, there are two types of DML triggers which are given below:-
After Triggers
Instead of Triggers
After Triggers:-
"After Triggers" are executed after the data modification action (Insert, Delete, Update) is executed on the respective tables. A table can have multiple triggers defined on it.
Syntax of the After trigger
Create Trigger trigger _name
On Table name
For Insert/Delete/update
As
Begin
//SQL Statements
End
Instead of Triggers
Instead of trigger is used when we want to perform another action instead of the action which causes the trigger to fire. Instead of trigger can be defined in case of Insert, Delete and Update. For example, suppose we have a condition that in a single transaction a user could not be able to debit more than $15000. We can use the Instead of trigger, to implement this constraint. If the user try to debit more than $15000 from his account at a time then error is raised with the message "Cannot Withdraw more than 15000 at a time".
Read More..
[1055 clicks]
Published under:
SQL Server Interview Questions · · · ·