SqlTriggerContext 类

定义

命名空间:

Microsoft.SqlServer.Server

程序集:

System.Data.dll

提供所激发的触发器的上下文信息。

public ref class SqlTriggerContext sealed

public sealed class SqlTriggerContext

type SqlTriggerContext = class

Public NotInheritable Class SqlTriggerContext

继承

Object

SqlTriggerContext

示例

以下示例演示了一个 SqlTriggerContext 对象，用于确定是否发生了某个 Insert 操作。 如果将行插入到 user的表中，将从插入的行中检索用户名和实名，然后将其添加到 UserNameAudit 表中。

[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;
   }
}

<SqlTrigger(Name:="UsersAudit", Target:="[dbo].[users]", Event:="FOR INSERT")> _
Public Shared Sub UsersAudit()
        
   Dim command As SqlCommand
   Dim triggContext As SqlTriggerContext
   Dim reader As SqlDataReader
   Dim userName As String
   Dim realName As String

         
   ' Get the trigger context.
   triggContext = SqlContext.TriggerContext        

   Select Case triggContext.TriggerAction
           
      Case TriggerAction.Insert

         ' Retrieve the connection that the trigger is using.
         Using connection As 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 = CType(reader(0), String)
            realName = CType(reader(1), String)

            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()
                 
          End Using
         
   End Select

End Sub

注解

提供的上下文信息包括导致触发器触发的操作类型、在 UPDATE 操作中修改了哪些列;如果是数据定义语言 (DDL) 触发器，则 XML EventData 结构 (请参阅描述触发操作的 Transact-SQL 参考) 。

当代码通过 属性在触发器中运行时，类中提供了 SqlContext 的TriggerContext实例SqlTriggerContext。

属性

ColumnCount	获取绑定到触发器的数据表所包含的列的数目。 此属性是只读的。
EventData	获取特定于激发触发器的操作的事件数据。
TriggerAction	指示激发触发器的操作。

方法

IsUpdatedColumn(Int32)

如果列受 INSERT 或 UPDATE 语句的影响，则返回 true。