Section 5.6. Triggers


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:

  1. 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
        )
     

  2. Using the Visual Studio 2005 IDE, create a new SQL Server project named DmlTrigger.

  3. Create a trigger item in the project. Name the item VolumeTriggers.cs.

  4. 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);
                    }
                }
            }
        }
     

  5. 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.

  6. 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
     

  7. 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:

  1. 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
            )
        )
     

  2. Using the Visual Studio 2005 IDE, create a new SQL Server project named DdlTrigger.

  3. Create a trigger item in the project. Name the item LogTableActivityTrigger.cs .

  4. 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.

  5. Build the solution.

  6. 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.
     

  7. 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>