Triggers

• DML Triggers
• DDL Triggers

(INCLUDE SYNTAX WITH EVERYTHING)

(INCLUDE NOTES FROM Advanced SQL book, PRAGMATIC WORKS INTRO AND ADVANCED, DATABASESTAR ACADEMY, SQL COOKBOOK, Udemy, WENZEL)

(COMPARE WITH ADVANCED SQL BOOK ONE LAST TIME)

Triggers

Triggers are stored procedures that cannot be executed explicitly. Instead it’s attached to an event, meaning whenever an event occurs the trigger fires and its code gets executed. SQL Server supports DML triggers (such as INSERT) and DDL triggers (such as CREATE TABLE). Triggers are used for tasks like auditing, enforcing integrity rules that cannot be enforced with constraints, and enforcing policies. Including a ROLLBACK TRAN command inside the trigger code causes a rollback of all changes that took place in the trigger, as well as all changes that took place in the transaction associated with the trigger.

Note that in SQL Server, triggers are fired per statement, and not per modified row.

DML Triggers

SQL Server supports two types of DML triggers: after and instead of. An after trigger can only be defined on permanent tables, and it fires after the event it’s associated with finishes. The inserted table holds the new image of the affected rows in the case of INSERT and UPDATE actions. The deleted table holds the old image of the affected rows in the case of DELETE and UPDATE actions. Note that INSERT, UPDATE, and DELETE actions can be executed by the INSERT, UPDATE, and DELETE statements, as well as by the MERGE statement. In the case of instead triggers, the inserted and deleted tables contain the rows that were supposed to be affected by the modification that caused the trigger to fire.

For our demo, we create tables dbo.T1 and dbo.T1_Audit, like follows:

(Include code, explanation, and results from page 409)

The dbo.T1_Audit holds audit information for insertions to dbo.T1. The audit_lsn column of dbo.T1_Audit has an identity property and represents an audit log serial number. The dt column represents the date and time of the insertion and has the default value of SYSDATETIME(). The login_name column represents the name of the login that performed the insertion, and has the default of ORIGINAL_LOGIN().

The following is an AFTER INSERT trigger trg_T1_insert_audit on dbo.t1 table to audit insertions:

(Include code, explanation, and results from page 409)

The trigger inserts into the audit table the result of a query against the inserted table. The values of the columns in the audit table that are not listed explicitly in the INSERT statement and are generated by the default expressions described in the previous code. The following code tests the trigger:

(Include code, explanation, and results from page 409)

The trigger fires after each statement. We then query the audit table, as follows:

(Include code, explanation, and results from page 409)

The result represents the date and time when we ran the inserts, as well as the login we used to connect to SQL Server.

The following code is executed for cleanup

(Include code, explanation, and results from page 410)

DDL Triggers

DDL triggers can be used for tasks like auditing, policy enforcement, and change management. The box product of SQL Server supports the creation of DDL triggers at two scopes, the database scope and server scope, depending on the scope of the event. Azure SQL Database only supports database triggers. Database triggers are created for events with a database scope (such as CREATE TABLE), whereas Server triggers are created for events with a server scope (such as CREATE DATABASE). SQL Server only supports after DDL triggers; instead triggers are not supported.

The EVENTDATA function returns information about the event that caused the trigger to fire, in the form of an XML instance. The even attributes such as post time, event time, and login name can be extracted from the XML instance using XQuery expressions.

The code to create our demo table dbo.AuditDDLEvents is as follows:

(Include code, explanation, and results from page 410)

Notice the eventdata column is of XML datatype. In addition to the individual attributes that the trigger extracts from the event information and stores in individual attributes, it also stores full event information in the eventdata column.

The following code create the audit trigger trg_audit_ddl_events on the database, using the event group DDL_DATABASE_LEVEL_EVENTS which represents DDL events at the database level:

(Include code, explanation, and results from page 410)

The trigger’s code first stores the event information obtained from the EVENTDATA function into the @eventdata variable, then inserts a row into the audit table with the attributes extracted using XQuery expressions by the .value method from the event information, plus the XML instance with the full event information.

The following code is used to test the trigger:

(Include code, explanation, and results from page 411)

The following code is then used to query the audit table:

(Include code, explanation, and results from page 411)

As we can see, the posttime and loginname attributes reflect the post time and logon name in our environment.

Lastly, the following code is executed for cleanup:

(Include code, explanation, and results from page 411-412)