Data Definition Language (DDL) Triggers in SQL

February 01, 2015 , , , 0 Comments

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:

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.

Nested Triggers

Nested triggers are fired due to actions of other triggers. For example, you delete a row from TableA. A trigger on TableA deletes rows from TableB. Because you are deleting rows from TableB, a trigger is executed on TableB to record the deleted rows.

Recursive Triggers

Recursive triggers are a special case of nested triggers. Unlike nested triggers, support for recursive triggers is at the database level. As the name implies, a recursive trigger eventually calls itself. There are two types of recursive triggers, Direct and Indirect.

Direct Recursive Trigger

A direct trigger is a trigger that performs the same operation (insert, update, or delete) on the same table causing the trigger to fire itself again.

Indirect Recursive Trigger

An indirect trigger is a trigger that fires a trigger on another table and eventually the nested trigger ends up firing the first trigger again. For instance, an UPDATE on TableA fires a trigger that in turn fires an update on TableB. The update on TableB fires another trigger that performs an update on TableC. TableC has a trigger that causes an update on TableA again. The update trigger of TableA is fired again.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google