Triggers in Transact SQL

Triggers are a special type of stored procedures that are executed automatically when a specific event occurs in the database.

The nature of these events can be DML (INSERT, UPDATE, DELETE), or DDL(CREATE, ALTER, DROP)

Triggers are therefore often used to automate processes in the database that will be executed under defined circumstances.

CREATE TRIGGER

The basic syntax for the creation and definition of a trigger DML is as follows:

CREATE TRIGGER triggerName
ON [table]|view
[ WITH <options> ]
{ FOR | AFTER | INSTEAD OF }
{ INSERT|UPDATE|DELETE}
AS [code]

The basic syntax for the creation and definition of a trigger DDL is as follows:

CREATE TRIGGER triggerName
ON ALL SERVER | DATABASE
[ WITH <options> ]
{ FOR | AFTER }
{ CREATE... | ALTER ...| DROP ...}
AS [code]

• The FOR or AFTER clause specifies that the trigger will only be executed after all the operations of the triggering SQL statement have started successfully. This clause cannot be used with triggers that operate on views.

• The INSTEAD OF clause specifies that the trigger should be fired instead of the triggering SQL statement. This clause may not be used with triggers that operate on DDL or LOGON triggers.

Next, we will see some examples to illustrate how triggers` work.

TRIGGER DML AFTER

This trigger will be in charge of updating a client’s account to NULL when any modification is made to said record:

CREATE TRIGGER bankAccountTrigger
    ON Customers AFTER UPDATE
    AS
      BEGIN
        DECLARE @customerId INT
        SELECT  @customerId = Customers.customerId
        FROM Customers INNER JOIN inserted
        ON Customers.customerId = inserted.customerId;
        UPDATE Customers SET bankAccount = NULL WHERE customerId = @customerId
      END
 GO

• The first thing we do internally in the trigger, is retrieve the specific customerId of the record on which the trigger is going to act. There are some specific temporary tables for them in the use of the triggers which are: inserted and deleted.

• Once the specific customerId is retrieved, we perform an UPDATE of the account to NULL for that client.

Next, we proceed to make a registry modification to check that the trigger is executed correctly:

UPDATE Customers SET [address] = '4822 Radio Park Drive' WHERE customerId = 1;

We consult the registry and verify that when modifying the address, the bankAccount has been updated to NULL due to the effect of the Trigger:

SELECT * FROM Customers WHERE customerId = 1;

TRIGGER DML INSTEAD OFF

The following trigger, has the purpose of returning a message before deleting any record from the Customers table:

CREATE TRIGGER triggerDeleteCustomer
    ON Customers INSTEAD OF DELETE
    AS
     PRINT `A record is going to be deleted from the Customers table`

To test the trigger execution, we proceed to delete any record from the Customers table:

 DELETE FROM Customers WHERE [name] ='"Richard' AND lastName = 'Otero';

We will observe that the console will show the message specified in the trigger:

TRIGGER DDL

This trigger has the purpose of returning a message whenever a table is deleted or modified.

 CREATE TRIGGER ddlTable
    ON DATABASE
    FOR  DROP_TABLE, ALTER_TABLE
    AS
     PRINT 'A permanent structural modification or deletion of a table has been made'

To test the trigger execution, we proceed to delete the existing table Employees from our database:

 DROP TABLE IF EXISTS Employees;

We will observe that the console will show the message specified in the Trigger:

ALTER TRIGGER

ALTER TRIGGER is the statement to alter triggers that had already been declared.

We are going to proceed to modify the trigger of INSTEAD OF: triggerDeleteCustomer, since in accordance with the rules of referential integrity, if a customerId is present as a Foreign Key in the Orders table, it cannot be removed from the Customers table.

The modification that we will make, therefore, will consist of recovering the customerId in a variable and deleting the associated record first in the Orders table, to later delete it in the Customers table and display the message defined in the Trigger.

 ALTER TRIGGER triggerDeleteCustomer
    ON Customers INSTEAD OF DELETE
    AS
        BEGIN
            DECLARE @customerId INT
            SELECT  @customerId = Customers.customerId
            FROM Customers INNER JOIN deleted
            ON Customers.customerId = deleted.customerId;

            DELETE FROM Orders WHERE customerId = @customerId
            DELETE FROM Customers WHERE customerId = @customerId

            PRINT 'A record will be deleted from the Customers table (and from the Orders table if it is present)'
        END

DROP TRIGGER

The DROP TRIGGER statement allows you to completely remove triggers generated within SQL Server.

DROP TRIGGER IF EXISTS bankAccountTrigger;

As in other types of DROP, it is recommended to always use the IF EXISTS clause.