SqlTriggerContext.TriggerAction Property

Definition

Namespace:

Microsoft.SqlServer.Server

Assembly:

System.Data.dll

Indicates what action fired the trigger.

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

Property Value

TriggerAction

The action that fired the trigger as a TriggerAction.

Examples

The following example shows an auditing trigger. If an Insert or Delete action occurred, the affected rows are retrieved from the INSERTED and DELETED tables.

[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