Triggers

SQL Server Usage

Triggers are special type of stored procedure that run automatically in response to events and are most commonly used for Data Manipulation Language (DML).

SQL Server supports AFTER/FOR and INSTEAD OF triggers, which can be created on tables and views. AFTER and FOR are synonymous. SQL Server also provides an event trigger framework at the server and database levels that includes Data Definition Language (DDL), Data Control Language (DCL), and general system events such as login.

Trigger Run

You can create AFTER triggers only on a table. You can create INSTEAD OF triggers on tables and views.

You can create only a single INSTEAD OF trigger for any given object and event. When multiple AFTER triggers exist for the same event and object, you can partially set the trigger order by using the sp_settriggerorder system stored procedure. It enables setting the first and last triggers to be run, but not the order of others.

Trigger Scope

SQL Server supports only statement level triggers. The trigger code runs only once for each statement. The data modified by the DML statement is available to the trigger scope and is saved in two virtual tables: INSERTED and DELETED. These tables contain the entire set of changes performed by the DML statement that caused trigger run.

SQL triggers always run within the transaction of the statement that triggered the run. If the trigger code issues an explicit ROLLBACK, or causes an exception that mandates a rollback, the DML statement is also rolled back. For INSTEAD OF triggers, the DML statement isn’t run and, therefore, doesn’t require a rollback.

Examples

Use a DML trigger to audit invoice deletions

The following example demonstrates how to use a trigger to log rows deleted from a table.

Create and populate the Invoices table.

CREATE TABLE Invoices
(
InvoiceID INT NOT NULL PRIMARY KEY,
Customer VARCHAR(20) NOT NULL,
TotalAmount DECIMAL(9,2) NOT NULL
);

INSERT INTO Invoices (InvoiceID,Customer,TotalAmount)
VALUES
(1, 'John', 1400.23),
(2, 'Jeff', 245.00),
(3, 'James', 677.22);

Create the InvoiceAuditLog table.

CREATE TABLE InvoiceAuditLog
(
    InvoiceID INT NOT NULL PRIMARY KEY,
    Customer VARCHAR(20) NOT NULL,
    TotalAmount DECIMAL(9,2) NOT NULL,
    DeleteDate DATETIME NOT NULL DEFAULT (GETDATE()),
    DeletedBy VARCHAR(128) NOT NULL DEFAULT (CURRENT_USER)
);

Create an AFTER DELETE trigger to log deletions from the Invoices table to the audit log.

CREATE TRIGGER LogInvoiceDeletes
ON Invoices
AFTER DELETE
AS
BEGIN
INSERT INTO InvoiceAuditLog (InvoiceID, Customer, TotalAmount)
SELECT InvoiceID,
    Customer,
    TotalAmount
FROM Deleted
END;

Delete an invoice.

DELETE FROM Invoices
WHERE InvoiceID = 3;

Query the content of both tables.

SELECT *
FROM Invoices AS I
FULL OUTER JOIN
InvoiceAuditLog AS IAG
ON I.InvoiceID = IAG.InvoiceID;

For the preceding example, the result looks as shown following.

InvoiceID  Customer TotalAmount  InvoiceID  Customer  TotalAmount  DeleteDate      DeletedBy
1          John     1400.23      NULL       NULL      NULL         NULL            NULL
2          Jeff     245.00       NULL       NULL      NULL         NULL            NULL
NULL       NULL     NULL         3          James     677.22       20180224 13:02  Domain/JohnCortney

Create a DDL trigger

Create a trigger to protect all tables in the database from accidental deletion.

CREATE TRIGGER PreventTableDrop
ON DATABASE FOR DROP_TABLE
AS
BEGIN
    RAISERROR ('Tables can't be dropped in this database', 16, 1)
    ROLLBACK TRANSACTION
END;

Test the trigger by attempting to drop a table.

DROP TABLE [Invoices];
    GO

The system displays the follow message indicating the Invoices table can’t be dropped.

Msg 50000, Level 16, State 1, Procedure PreventTableDrop, Line 5 [Batch Start Line 56]
Tables Can't be dropped in this database
Msg 3609, Level 16, State 2, Line 57
The transaction ended in the trigger. The batch has been aborted.

For more information, see DML Triggers and DDL Triggers in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) provides Data manipulation Language (DML) triggers only.

MySQL supports BEFORE and AFTER triggers for INSERT, UPDATE, and DELETE with full control over trigger run order.

MySQL triggers differ substantially from SQL Server. However, you can migrate most common use cases with minimal code changes. The following list identifies the major differences between the SQL Server and Aurora MySQL triggers:

Syntax

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER <Trigger Name>
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON <Table Name>
FOR EACH ROW
[{ FOLLOWS | PRECEDES } <Other Trigger Name>]
<Trigger Code Body>

Examples

Use a DML trigger to audit invoice deletions

The following example demonstrates how to use a trigger to log rows deleted from a table.

Create and populate the Invoices table.

CREATE TABLE Invoices
(
    InvoiceID INT NOT NULL PRIMARY KEY,
    Customer VARCHAR(20) NOT NULL,
    TotalAmount DECIMAL(9,2) NOT NULL
);

INSERT INTO Invoices (InvoiceID, Customer, TotalAmount)
VALUES
(1, 'John', 1400.23),
(2, 'Jeff', 245.00),
(3, 'James', 677.22);

Create the InvoiceAuditLog table.

CREATE TABLE InvoiceAuditLog
(
    InvoiceID INT NOT NULL
        PRIMARY KEY,
    Customer VARCHAR(20) NOT NULL,
    TotalAmount DECIMAL(9,2) NOT NULL,
    DeleteDate DATETIME NOT NULL
        DEFAULT (GETDATE()),
    DeletedBy VARCHAR(128) NOT NULL
        DEFAULT (CURRENT_USER)
);

Create a trigger to log deleted rows.

CREATE OR REPLACE TRIGGER LogInvoiceDeletes
ON Invoices
FOR EACH ROW
AFTER DELETE
AS
    BEGIN
    INSERT INTO InvoiceAuditLog (InvoiceID, Customer, TotalAmount, DeleteDate, DeletedBy)
    SELECT InvoiceID,
        Customer,
        TotalAmount,
        NOW(),
        CURRENT_USER()
    FROM OLD
END;

Test the trigger by deleting an invoice.

DELETE FROM Invoices
WHERE InvoiceID = 3;

Select all rows from the InvoiceAuditLog table.

SELECT * FROM InvoiceAuditLog;

For the preceding example, the result looks as shown following.

InvoiceID  Customer  TotalAmount  DeleteDate      DeletedBy
3          James     677.22       20180224 13:02  George

Summary

For more information, see Trigger Syntax and Examples and CREATE TRIGGER Statement in the MySQL documentation.