Triggers are a special type of stored procedure which is executed automatically based on the occurrence of a database event. These events can be categorized as:
• Data Manipulation Language (DML) and
• Data Definition Language (DDL) events.
The benefit derived from triggers is based in their events driven nature. Once created, the trigger automatically fires without user intervention based on an event in the database.
A. Using DML Triggers: DML triggers are invoked when any DML command such as INSERT, DELETE, and UPDATE occurs on the data of a table and/or view.
• DML triggers are powerful objects for maintaining database integrity and consistency.
• DML triggers evaluate data before it has been committed to the database. o During this evaluation the following actions are performed.
We cannot use the following commands in DML trigger:
o ALTER DATABASE
o CREATE DATABASE
o DISK DATABASE
o LOAD DATABASE
o RESTORE DATABASE
B. Using DDL Triggers:
• These triggers focus on changes to the definition of database objects as opposed to changes to the actual data.
• This type of trigger is useful for controlling development and production database environments.
Let us create DDL trigger now-
The following is the syntax.
CREATE TRIGGER trigger_name ON{ALL SERVER | DATABASE }
[WITH < ddl_trigger_option > [, ...n]]{ FOR | AFTER } {
event_type | event_group }[, ...n] AS
{sql_statement[;][...n] | EXTERNAL NAME < method specifier > [;] }
CREATE TRIGGER tr_TableAudit ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS PRINT 'You must disable the TableAudit trigger in order to change any table in this database ' ROLLBACK GO
