- MS Sql Server
- 962
- March-20-2018
- by Rizwan Saqib
Triggers
Definition
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view
Types of Triggers
1.DML Triggers [Data manipulation language]
2. DDL Triggers [Data Definition Language]
3. LOGON Triggers
Define Triggers
1.DML Triggers
In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML like operations. DML Triggers are of two types
1.
After Trigger (using FOR/AFTER CLAUSE)
This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.
Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
2.
Instead of Trigger (using INSTEAD OF CLAUSE)
This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
2.DDL Triggers
In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like soperations.
Note:
We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.
DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.
