SQL Server : Programming DDL Triggers

Programming DDL Triggers

Syntax :

 CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE }
  [ WITH < ddl_trigger_option > [,....n] ]
 { FOR | AFTER } { event_type | event_group } [,....n]
  
  AS 
 { sql_stmt [;] [...n] | EXTERNAL NAME < method specifier ? [;]  }

Example :

The following example illustrates how a DDL trigger can be used to prevent any table in a database from being modified or dropped.

CREATE TRIGGERR safety
ON DATABASE
FOR DROP_TABLE.ALTER_TABLE
AS
  PRINT 'You must disable Trigger "Safety" to drop or alter tables!'
  ROLLBACK;

Altering a Triggers

The definition of an existing trigger can be altered without dropping it. The altered definition replaces the definition of the existing trigger with the new definition.

Syntax :

ALTER TRIGGER trigger_name
ON { table | view }
 {
   { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [.] [ UPDATE] }
     AS
     SQL _ STATEMENTS [...n ]
   }
 }

Example :

This example creates a trigger that prints a user-defined message to the client when a user tries to add or change data in the emp table. Then, the trigger is altered using ALTER TRIGGER to apply the trigger only on INSERT activates. This trigger is helpful because it reminds the user who updates or inserts rows into this table.

Create trigger trig1
 ON emp
 With Encryption
 For INSERT, UPDATE
 As RAISERROR(40008, 16, 10)
ALTER Trigger trig1
 ON emp
 FOR INSERT
 AS RAISERROR(40008, 16, 10)

Dropping a Triggers

A trigger can be dropped by using, DROP TRIGGER statement. A trigger gets dropped automatically when its associated table is dropped. Permissions to drop a trigger defaults to the table owner. But the members of the system administrators(sysadmin) and the database owner(db_owner) can drop any object by specifying the owner in the DROP TRIGGER statement.

Syntax :

 DROP TRIGGER trigger_name 

Disabling or Enabling a Triggers

Triggers can be enabled or disabled using ALTER TABLE statement. On disabling trigger, the actions in the trigger are not performed until the trigger is re-enabled.

Syntax :

 ALTER TABLE table_name
 { ENABLE | DISABLE } TRIGGER
 { ALL | trigger_name [, --n]

Setting the Trigger Order

The 'sp_settriggerorder' is used to specify the order of firing the trigger. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.

Syntax :

sp_settriggerorder [@triggername =] '[ triggerschema. ]  triggername', [ @order =] 'value', [@stmttype =] 'statement_type' 
 [, [@namespace =] { 'DATABASE' | 'SERVER' | NULL } ]

Example :

1. Setting the order of DML triggers

 USE AdventureWorks;
 Go
 sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader', @order= 'First', @stmttye= 'UPDATE';

2. Setting the order of DDL triggers

 USE AdventureWorks;
 Go
 sp_settriggerorder @triggername='ddlDatabaseTriggerLog', @order='First', @stmttype='ALTER_TABLE', @namespace= 'DATABASE';