SqlTriggerContext.TriggerAction Propiedad

Definición

Espacio de nombres:

Microsoft.SqlServer.Server

Ensamblado:

System.Data.dll

Indica qué acción desencadenó el activador.

public:
 property Microsoft::SqlServer::Server::TriggerAction TriggerAction { Microsoft::SqlServer::Server::TriggerAction get(); };

public Microsoft.SqlServer.Server.TriggerAction TriggerAction { get; }

member this.TriggerAction : Microsoft.SqlServer.Server.TriggerAction

Public ReadOnly Property TriggerAction As TriggerAction

Valor de propiedad

TriggerAction

La acción que desencadenó el activador, como TriggerAction.

Ejemplos

En el ejemplo siguiente se muestra un desencadenador de auditoría. Si se ha producido una Insert acción o Delete , las filas afectadas se recuperan de las tablas INSERTED y DELETED.

[SqlTrigger(Name = @"TableAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, DELETE")]
public static void TableAudit()
{
    SqlCommand command = new SqlCommand();
    SqlTriggerContext triggContext = SqlContext.TriggerContext;
    SqlDataReader reader;

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

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

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

                // Retrieve data from inserted row.

                reader.Close();
            }
            break;

        // Delete.
        case TriggerAction.Delete:
            using (SqlConnection connection
                   = new SqlConnection(@"context connection=true"))
            {
                // Open the context connection.
                connection.Open();

                // Get the deleted rows.
                command = new SqlCommand(@"SELECT * FROM DELETED;",
                                         connection);
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // Retrieve data from deleted rows.
                    }

                    reader.Close();
                }
                else
                {
                    // No rows affected.
                }
            }

            break;
    }
}

<SqlTrigger(Name:="TableAudit", Target:="[dbo].[Users]", Event:="FOR INSERT, DELETE")> _
Public Shared Sub TableAudit()
    Dim command As SqlCommand
    Dim triggContext As Microsoft.SqlServer.Server.SqlTriggerContext
    Dim reader As SqlDataReader

    triggContext = SqlContext.TriggerContext

    Select Case triggContext.TriggerAction

        ' Insert.
        Case TriggerAction.Insert
            Using connection As New SqlConnection("context connection=true")

                ' Open the context connection.
                connection.Open()

                ' Get the inserted row.
                command = New SqlCommand("SELECT * FROM INSERTED;", connection)
                reader = command.ExecuteReader()
                reader.Read()

                ' Retrieve data from inserted row.

                reader.Close()
            End Using

        ' Delete.
        Case TriggerAction.Delete
            Using connection As New SqlConnection("context connection=true")

            ' Open the context connection.
            connection.Open()

            ' Get the deleted rows.
            command = New SqlCommand("SELECT * FROM DELETED;", connection)

            reader = command.ExecuteReader()

            If reader.HasRows Then

                While reader.Read()

                    ' Retrieve data from deleted rows

                End While

                reader.Close()

           Else
               ' No rows affected.
           End If

       End Using
    End Select
End Sub