5.6. Triggers A trigger is a type of stored procedure that executes automatically when an event occurs. SQL Server has two types of triggers :
Data Manipulation Language (DML) trigger Executes when INSERT, UPDATE, and DELETE commands modify data in a table or view. Figure 5-8. Results for UDT example 
Data Definition Language (DDL) trigger Executes in response to a DDL statement that is often used to make database schema changes. Examples include the CREATE, ALTER, and DROP statements. A CLR trigger is implemented as a method of a class in a .NET Framework assembly. The following two sections discuss creating CLR DML and DDL triggers . 5.6.1. DML Triggers A CLR trigger is implemented as a public static void method in a .NET Framework assembly. You identify a CLR DML trigger by marking the method that implements the trigger with the SqlTrigger attribute, which indicates that a method should be registered as a DML trigger. The SqlTrigger attribute has the following syntax: SqlTrigger [ ( trigger-attribute [ ,... ] ) ] trigger-attribute::= Target = "table-name" | Event = "trigger-type update-action [, ...]" trigger-type::= FOR | AFTER | INSTEAD OF update-action::= UPDATE | DELETE | INSERT
where:
Target = "table-name" Specifies the table to which the trigger applies
trigger-type Specifies the type of trigger
update-action Specifies the DML action that activates the triggerUPDATE, DELETE, or INSERT You can use the triggerAction property of the SqlTriggerContext class instead of the SqlTrigger attribute. This is discussed later in this section in the example about creating a DDL trigger. The following example creates update, insert, and delete DML triggers that log updates, inserts, and deletes to a table named Volume. These events are logged to a table named VolumeAudit. The example then registers the triggers and shows the results of executing DML statements against the Volume table. Follow these steps: Execute the following T-SQL statements to create the Volume and VolumeAudit tables that are the target and logging destination of the triggers: USE ProgrammingSqlServer2005 GO CREATE TABLE Volume ( ID int NOT NULL, Length float NOT NULL, Width float NOT NULL, Height float NOT NULL, Volume float NOT NULL CONSTRAINT DF_Area_Area DEFAULT ((0)), CONSTRAINT PK_Volume PRIMARY KEY CLUSTERED ( ID ASC ) ) GO CREATE TABLE VolumeAudit ( Action varchar(50) NOT NULL, Description varchar(max) NOT NULL )
Using the Visual Studio 2005 IDE, create a new SQL Server project named DmlTrigger. Create a trigger item in the project. Name the item VolumeTriggers.cs. Replace the code in VolumeTriggers.cs with the following code: using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server; using System.Data.SqlClient; using System.Collections; public partial class Triggers { [SqlTrigger (Target="Volume", Event="FOR INSERT")] public static void InsertTrigger( ) { using (SqlConnection conn = new SqlConnection("context connection=true")) { SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM INSERTED", conn); DataTable dt = new DataTable( ); da.Fill(dt); SqlCommand cmd = new SqlCommand( ); cmd.Connection = conn; conn.Open( ); foreach (DataRow row in dt.Rows) { int id = (int)row[0]; double length = (double)row[1]; double width = (double)row[2]; double height = (double)row[3]; double volume = length * width * height; string audit = string.Format("ID = {0}, Length = {1}, " + "Width = {2}, Height = {3}", id, length, width, height); cmd.CommandText = "INSERT INTO VolumeAudit VALUES ('INSERTED', '" + audit + "')"; cmd.ExecuteNonQuery( ); cmd.CommandText = "UPDATE Volume SET Volume = " + volume + " WHERE ID = " + id; cmd.ExecuteNonQuery( ); SqlPipe pipe = SqlContext.Pipe; pipe.Send("Row inserted: " + audit); } } } [SqlTrigger(Target = "Volume", Event = "FOR UPDATE")] public static void UpdateTrigger( ) { using (SqlConnection conn = new SqlConnection("context connection=true")) { SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM DELETED", conn); DataTable dtDel = new DataTable( ); da.Fill(dtDel); da = new SqlDataAdapter("SELECT * FROM INSERTED", conn); DataTable dtIns = new DataTable( ); da.Fill(dtIns); SqlCommand cmd = new SqlCommand( ); cmd.Connection = conn; conn.Open( ); for (int i = 0; i < dtDel.Rows.Count; i++) { DataRow rowDel = dtDel.Rows[i]; int delId = (int)rowDel[0]; double delLength = (double)rowDel[1]; double delWidth = (double)rowDel[2]; double delHeight = (double)rowDel[3]; double delVolume = (double)rowDel[4]; string delAudit = string.Format("ID = {0}, Length = {1}, " + "Width = {2}, Height = {3}, Volume = {4}", delId, delLength, delWidth, delHeight, delVolume); DataRow rowIns = dtIns.Rows[i]; int insId = (int)rowIns[0]; double insLength = (double)rowIns[1]; double insWidth = (double)rowIns[2]; double insHeight = (double)rowIns[3]; double insVolume = insLength * insWidth * insHeight; string insAudit = string.Format("ID = {0}, Length = {1}, " + "Width = {2}, Height = {3}, Volume = {4}", insId, insLength, insWidth, insHeight, insVolume); cmd.CommandText = "UPDATE Volume SET Volume = " + insVolume + " WHERE ID = " + insId; cmd.ExecuteNonQuery( ); cmd.CommandText = "INSERT INTO VolumeAudit VALUES " + "('UPDATED', 'Original: " + delAudit + "; " + "New: " + insAudit + "')"; cmd.ExecuteNonQuery( ); SqlPipe pipe = SqlContext.Pipe; pipe.Send("Row updated: Original: " + delAudit + "; " + "New: " + insAudit); } } } [SqlTrigger(Target = "Volume", Event = "FOR DELETE")] public static void DeleteTrigger( ) { using (SqlConnection conn = new SqlConnection("context connection=true")) { SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM DELETED", conn); DataTable dt = new DataTable( ); da.Fill(dt); SqlCommand cmd = new SqlCommand( ); cmd.Connection = conn; conn.Open( ); foreach(DataRow row in dt.Rows) { int id = (int)row[0]; double length = (double)row[1]; double width = (double)row[2]; double height = (double)row[3]; double volume = (double)row[4]; string audit = string.Format("ID = {0}, Length = {1}, " + "Width = {2}, Height = {3}, Volume = {4}", id, length, width, height, volume); cmd.CommandText = "INSERT INTO VolumeAudit VALUES ('DELETED', '" + audit + "');"; cmd.ExecuteNonQuery( ); SqlPipe pipe = SqlContext.Pipe; pipe.Send("Row deleted: " + audit); } } } }
Build the solution. Each of the three triggers is marked with the SqlTrigger attribute that specifies the Volume table as the target of the trigger together with the event that causes each trigger to execute. In SQL Server Management Studio, register the assembly and create the DML insert, update, and delete triggers by executing the following query: USE ProgrammingSqlServer2005 GO CREATE ASSEMBLY VolumeTriggers FROM 'C:\PSS2005\DmlTrigger\DmlTrigger\bin\Debug\DmlTrigger.dll' GO CREATE TRIGGER VolumeInsertTrigger ON Volume FOR INSERT AS EXTERNAL NAME VolumeTriggers.Triggers.InsertTrigger GO CREATE TRIGGER VolumeUpdateTrigger ON Volume FOR UPDATE AS EXTERNAL NAME VolumeTriggers.Triggers.UpdateTrigger GO CREATE TRIGGER VolumeDeleteTrigger ON Volume FOR DELETE AS EXTERNAL NAME VolumeTriggers.Triggers.DeleteTrigger GO
Execute the following T-SQL statements to insert two rows into the Volume table: INSERT INTO Volume (ID, Length, Width, Height) VALUES (1, 2.2, 3.4, 5.7) INSERT INTO Volume (ID, Length, Width, Height) VALUES (2, 6, 2, 5.4)
The results are shown in Figure 5-9. Figure 5-9. Results for DML trigger example  The output is generated by the following code in the insert DML trigger: pipe.Send("Row inserted: " + audit);
The VolumeAudit table now contains the following records: Action | Description |
|---|
INSERTED | ID = 1, Length = 2.2, Width = 3.4, Height = 5.7
| UPDATED | Original: ID = 1, Length = 2.2, Width = 3.4, Height = 5.7, Volume = 0; New: ID = 1, Length = 2.2, Width = 3.4, Height = 5.7, Volume = 42.636
| INSERTED | ID = 2, Length = 6, Width = 2, Height = 5.4
| UPDATED | Original: ID = 2, Length = 6, Width = 2, Height = 5.4, Volume = 0; New: ID = 2, Length = 6, Width = 2, Height = 5.4, Volume = 64.8
|
 | Although the update trigger on the Volume table updates the Volume table, the query is not recursive as long as the RECURSIVE_TRIGGERS database option is set to OFFthis is the default. You can check the status of all database options by executing the following T-SQL statement: SELECT * FROM sys.databases
The is_recursive_triggers_on column contains the setting of the RECURSIVE_TRIGGERS option for each database. You can change the value of a database option using the ALTER DATABASE statement. For example, execute the following T-SQL statement to change the recursive trigger behavior for the ProgrammingSqlServer2005 database to ON: ALTER DATABASE ProgrammingSqlServer2005 SET RECURSIVE_TRIGGERS ON
|
|
There are four recordstwo inserted by the insert DML trigger and two inserted by the update DML trigger when the insert DML trigger updates the Volume field. Execute the following T-SQL statement to update the first of the two rows previously inserted into the Volume table: UPDATE Volume SET Length = 1, Width = 4, Height = 7.2 WHERE ID = 1
The output follows: Row updated: Original: ID = 1, Length = 2.2, Width = 3.4, Height = 5.7, Volume = 42.636; New: ID = 1, Length = 1, Width = 4, Height = 7.2, Volume = 28.8 (1 row(s) affected)
The VolumeAudit table now contains a new record inserted by the update DML trigger: Action | Description |
|---|
UPDATED | Original: ID = 1, Length = 2.2, Width = 3.4, Height = 5.7, Volume = 42.636; New: ID = 1, Length = 1, Width = 4, Height = 7.2, Volume = 28.8
|
Execute the following T-SQL statement to delete the two rows from the Volume table: DELETE FROM Volume
The output follows: Row deleted: ID = 2, Length = 6, Width = 2, Height = 5.4, Volume = 64.8 Row deleted: ID = 1, Length = 1, Width = 4, Height = 7.2, Volume = 28.8 (2 row(s) affected)
The VolumeAudit table now contains two new records inserted by the delete DML trigger: Action | Description |
|---|
DELETED | ID = 2, Length = 6, Width = 2, Height = 5.4, Volume = 64.8
| DELETED | ID = 1, Length = 1, Width = 4, Height = 7.2, Volume = 28.8
|
5.6.2. DDL Triggers A CLR trigger is implemented as a public static void method in a .NET Framework assembly. Instead of using the SqlTrigger attribute to define events for which a DDL trigger executes, the SqlTriggerContext is used to get context information about the trigger. This SqlTriggerContext class cannot be instantiated directlycall the TRiggerContext property of the SqlContext class to get an instance. The SqlTriggerContext class has a TRiggerAction property that indicates the action that caused a trigger to fire. For DML triggers, the value can be triggerAction.Update, triggerAction.Insert, or TRiggerAction.Delete. There are many DDL trigger actionssee Microsoft SQL Server 2005 Books Online for a complete list. The following example creates, registers, and demonstrates a CLR DDL trigger that logs CREATE_TABLE and DROP_TABLE events to a table named Log. Follow these steps: Create a table named Log to store the DDL event information in: USE ProgrammingSqlServer2005 GO CREATE TABLE Log ( LogID int IDENTITY(1,1) NOT NULL, LogEntry varchar(max) NOT NULL, CONSTRAINT PK_Log PRIMARY KEY CLUSTERED ( LogID ASC ) )
Using the Visual Studio 2005 IDE, create a new SQL Server project named DdlTrigger. Create a trigger item in the project. Name the item LogTableActivityTrigger.cs . Replace the code in LogTableActivityTrigger.cs with the following code: using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server; using System.Data.SqlClient; public partial class Triggers { public static void LogTableActivityTrigger( ) { SqlTriggerContext tc = SqlContext.TriggerContext; using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open( ); SqlCommand cmd = new SqlCommand( ); cmd.Connection = conn; if (tc.TriggerAction == TriggerAction.CreateTable || tc.TriggerAction == TriggerAction.DropTable) { cmd.CommandText = "INSERT INTO Log VALUES " + "('" + tc.EventData.Value + "')"; cmd.ExecuteNonQuery( ); } } } }
A single DDL trigger is defined in the triggers class. The trigger checks the triggerAction property of the SqlTriggerContext and then logs the Eventdata for the event that caused this trigger to fire. In this example, it is not necessary to check the trigger context, as all events for which the trigger is registered in Step 6 execute the same code to log the event. You could use the triggerAction property to perform different actions for each of the different events that a DDL trigger is registered to handle. Build the solution. Register the assembly and create the DDL trigger by executing this statement in SQL Server Management Studio: USE ProgrammingSqlServer2005 GO CREATE ASSEMBLY DdlTrigger FROM 'C:\PSS2005\DdlTrigger\DdlTrigger\bin\Debug\DdlTrigger.dll' GO CREATE TRIGGER LogTableActivity ON DATABASE FOR CREATE_TABLE, DROP_TABLE AS EXTERNAL NAME DdlTrigger.Triggers.LogTableActivityTrigger The CREATE TRIGGER statement creates a DDL trigger that executes when CREATE TABLE and DROP TABLE DDL statements are executed.
Execute the following T-SQL statement to create and then drop a table named TestTable: USE ProgrammingSqlServer2005 GO CREATE TABLE TestTable ( TestID int NOT NULL, CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED ( TestID ASC ) ) GO DROP TABLE TestTable GO
The Log table contains two rows, shown here, containing details about the DDL CREATE_TABLE and DROP_TABLE events: <EVENT_INSTANCE> <EventType>CREATE_TABLE</EventType> <PostTime>2005-06-15T13:57:10.733</PostTime> <SPID>54</SPID> <ServerName>BILLHAMILTON1</ServerName> <LoginName>BILLHAMILTON1\whamilton</LoginName> <UserName>dbo</UserName> <DatabaseName>ProgrammingSqlServer2005</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>TestTable</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText> CREATE TABLE TestTable ( TestID int NOT NULL, CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED ( TestID ASC ) ) </CommandText> </TSQLCommand> </EVENT_INSTANCE> <EVENT_INSTANCE> <EventType>DROP_TABLE</EventType> <PostTime>2005-06-15T13:57:10.937</PostTime> <SPID>54</SPID> <ServerName>BILLHAMILTON1</ServerName> <LoginName>BILLHAMILTON1\whamilton</LoginName> <UserName>dbo</UserName> <DatabaseName>ProgrammingSqlServer2005</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>TestTable</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText> DROP TABLE TestTable </CommandText> </TSQLCommand> </EVENT_INSTANCE>
|
