SqlTriggerContext Class

Definition

Namespace:

Microsoft.SqlServer.Server

Assembly:

System.Data.dll

Provides contextual information about the trigger that was fired.

C#

public sealed class SqlTriggerContext

Inheritance

Object

SqlTriggerContext

Examples

The following example shows a SqlTriggerContext object being used to determine if an Insert action occurred. If a row was inserted into the user's table, the user name and real name are retrieved from the inserted row and then added to the UserNameAudit table.

C#

[SqlTrigger(Name = @"UsersAudit", Target = "[dbo].[users]", Event = "FOR INSERT")]
public static void UsersAudit()
{
   // Get the trigger context.
   string userName;
   string realName;
   SqlCommand command;
   SqlTriggerContext triggContext = SqlContext.TriggerContext;
   SqlDataReader reader;

   switch (triggContext.TriggerAction)
   {
      case TriggerAction.Insert:

      // Retrieve the connection that the trigger is using.
      using (SqlConnection connection
         = new SqlConnection(@"context connection=true"))
      {
         connection.Open();

         // Get the inserted row.
         command = new SqlCommand(@"SELECT * FROM INSERTED;",
                                  connection);

         // Get the user name and real name of the inserted user.
         reader = command.ExecuteReader();
         reader.Read();
         userName = (string)reader[0];
         realName = (string)reader[1];
         reader.Close();

         // Insert the user name and real name into the auditing table.
         command = new SqlCommand(@"INSERT [dbo].[UserNameAudit] (userName, realName) "
                  + @"VALUES (@userName, @realName);", connection);

         command.Parameters.Add(new SqlParameter("@userName", userName));
         command.Parameters.Add(new SqlParameter("@realName", realName));

         command.ExecuteNonQuery();
      }

      break;
   }
}

Remarks

The contextual information provided includes the type of action that caused the trigger to fire, which columns were modified in an UPDATE operation, and, in the case of a data definition language (DDL) trigger, an XML EventData structure (see Transact-SQL Reference) that describes the triggering operation.

An instance of SqlTriggerContext is available from the SqlContext class, when the code is running inside a trigger through the TriggerContext property.

Properties

ColumnCount	Gets the number of columns contained by the data table bound to the trigger. This property is read-only.
EventData	Gets the event data specific to the action that fired the trigger.
TriggerAction	Indicates what action fired the trigger.

Methods

IsUpdatedColumn(Int32)

Returns true if a column was affected by an INSERT or UPDATE statement.

Applies to