SQL | After Triggers
After Triggers are DML Triggers
After trigger fires, when SQL Server completes the triggering action successfully, that fired it. Generally, this trigger is executed when a table completes an insert, update or delete operations.
It is not supported in views. Sometimes it is known as FOR triggers.
After trigger can be of 3 types as below: -
AFTER INSERT Trigger
AFTER UPDATE Trigger
AFTER DELETE Trigger
The trigger connected to an insert operation on a table won't fire
until the row has satisfied all constraints, including the main key
constraint.
The AFTER trigger cannot be fired by SQL Server if the data insertion failed.
For/After DML Insert Trigger
This trigger should restrict the INSERT operation on the Employee table
Let’s try to insert the following record into the student table.
When you try to execute the above Insert statement it gives you the below error. First, the INSERT statement is executed, and then immediately this trigger fired and roll back the INSERT operation as well as print the message.
For/After DML Update Trigger
This trigger should restrict the UPDATE operation on the Student Marks table
Let’s try to update marks of student. once Student session completed.
When you try to execute the above Update statement it will give you the following error. First, the UPDATE statement is executed, and then immediately this trigger fired and roll back the UPDATE operation as well as print the message.
For/After DML Delete Trigger
This trigger should restrict the DELETE operation on the Student Marks table
Let’s try to update marks of student. once Student session completed.
When
you try to execute the above Delete statement it will give you the
following error. First, the DELETE statement is executed, and then
immediately this trigger fired and roll back the DELETE operation as
well as print the message.
