A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing.
Database auditing helps in monitoring the DDL operations on a database. DDL operation can include operations such as creation of a table or view, or modifications of a table or procedure. Consider an example, where you want the database administrator to be notified whenever a table is created in the Master Database. For this purpose, you can create a DDL trigger.
Depending on the way in which triggers are fired, they are categorized as:
By default, if more than one after trigger is created on a table is for a DML operation such as insert, update, or delete, then the sequence of execution is the order in which they were created.
For example, the EmpSalary table stores the salary and tax details for all the employees in an organization. You need to ensure that after the salary details of an employee are updated in the EmpSalary table, the tax details are also recalculated and updated. In such a scenario, you can implement an after trigger to update the tax details when the salary details are updated.
An instead of trigger can be used for the following actions:
For example, if a view is created with multiple columns from two or more tables, then an insert operation on the view is only possible if the primary key fields from all the base tables are used in the query. Alternatively, if you use an instead of trigger, you can insert data in the base tables individually. This makes the view logically updateable.
You can even create an Instead of trigger to restrict deletion in a master table. For example, you can display a message “Master record cannot be deleted” if a delete statement is executed on the Employee table of the AdventureWorks database.
Unlike after triggers, you cannot, create more than one Instead of trigger for a DML operation on the same table or view.
Database auditing helps in monitoring the DDL operations on a database. DDL operation can include operations such as creation of a table or view, or modifications of a table or procedure. Consider an example, where you want the database administrator to be notified whenever a table is created in the Master Database. For this purpose, you can create a DDL trigger.
Depending on the way in which triggers are fired, they are categorized as:
After Triggers
The after trigger can be created on any table for the insert, update or delete operation just like other triggers. The main difference in the functionality of an after trigger is that it is fired after the execution of the DML operation for which it has been defined. The after trigger is executed when all the constraints and triggers defined on the table are successfully executed.By default, if more than one after trigger is created on a table is for a DML operation such as insert, update, or delete, then the sequence of execution is the order in which they were created.
For example, the EmpSalary table stores the salary and tax details for all the employees in an organization. You need to ensure that after the salary details of an employee are updated in the EmpSalary table, the tax details are also recalculated and updated. In such a scenario, you can implement an after trigger to update the tax details when the salary details are updated.
Instead of Triggers
The instead of triggers can be primarily used to perform an action, such as a DML operation on another table or view. This type of trigger can be created on both a table as well as a view.An instead of trigger can be used for the following actions:
- Ignoring parts of a batch.
- Not processing a part of a batch and logging the problem rows.
- Taking an alternative action when an error condition is encountered.
For example, if a view is created with multiple columns from two or more tables, then an insert operation on the view is only possible if the primary key fields from all the base tables are used in the query. Alternatively, if you use an instead of trigger, you can insert data in the base tables individually. This makes the view logically updateable.
You can even create an Instead of trigger to restrict deletion in a master table. For example, you can display a message “Master record cannot be deleted” if a delete statement is executed on the Employee table of the AdventureWorks database.
Unlike after triggers, you cannot, create more than one Instead of trigger for a DML operation on the same table or view.