Development
010 February 2023

Triggers in MSSQL

A trigger is a special type of stored procedure in Microsoft SQL Server that automatically executes when an event occurs, such as an INSERT, UPDATE, or DELETE statement. Triggers can be used to enforce business rules, maintain data integrity, or perform actions automatically in response to changes in the data.

Here is an example of creating a trigger in SQL Server:

CREATE TRIGGER tr_example
ON table_name
AFTER INSERT
AS
BEGIN
    -- perform some action in response to the INSERT statement
END;

In this example, the trigger tr_example is created on the table_name table and will execute automatically after an INSERT statement is executed on that table. The code within the trigger defines the action that should be taken in response to the INSERT statement.

Control INSERT, UPDATE and DELETE events in one trigger?

CREATE TRIGGER tr_example
ON table_name
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    -- perform some action in response to the INSERT, UPDATE, or DELETE statement
END;

In this example, the trigger tr_example is created on the table_name table and will execute automatically after either an INSERT, UPDATE, or DELETE statement is executed on that table. The code within the trigger defines the action that should be taken in response to the INSERT, UPDATE, or DELETE statement.

Please note that "BEFORE" trigger is not directly available in MSSQL. You can have AFTER/FOR or INSTEAD OF triggers in MSSQL. (Google search will lead you many posts/videos for BEFORE trigger in MSSQL.)

How will i know which event is happenning?

In a SQL Server trigger, you can access the current data in the affected table by using the DELETED and INSERTED virtual tables. The DELETED table contains a copy of the data that was present in the table before the UPDATE or DELETE operation was executed, while the INSERTED table contains a copy of the data that will be inserted into the table after the INSERT or UPDATE operation is executed.

You can use these virtual tables to determine what data has changed and to make decisions about what actions to take based on the changes.

CREATE TRIGGER tr_example
ON table_name
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        IF EXISTS (SELECT * FROM INSERTED)
        BEGIN
            -- this is an UPDATE event
            -- perform some action based on the changes made to the data
        END
        ELSE
        BEGIN
            -- this is a DELETE event
            -- perform some action based on the deleted data
        END
    END
    ELSE
    BEGIN
        -- this is an INSERT event
        -- perform some action based on the inserted data
    END
END;

Lifecycle of AFTER INSERT trigger in MS SQL Server

The lifecycle of an AFTER INSERT trigger in SQL Server can be broken down into the following steps:

  1. An INSERT statement is executed on the associated table.
  2. The data for the inserted rows is stored in the inserted virtual table.
  3. The trigger AFTER INSERT is executed, and it can access the data in the inserted virtual table.
  4. The trigger performs any desired actions, such as logging the changes, validating the data, or updating related tables.
  5. The trigger completes execution, and control returns to the calling application.

It's important to note that the AFTER INSERT trigger is executed after the data has been successfully inserted into the table, so you can use the trigger to perform additional actions based on the inserted data. However, it's also possible to perform any necessary validation or data manipulation in the trigger to ensure that the data being inserted is valid before it's inserted into the table.

In general, the AFTER INSERT trigger is useful for performing actions that need to be performed after the data has been inserted into the table, such as logging, auditing, or data validation. The trigger can also be used to update related tables or perform complex operations on the inserted data.

Lifecycle of AFTER UPDATE trigger in MS SQL Server

The lifecycle of an AFTER UPDATE trigger in SQL Server can be broken down into the following steps:

  1. An UPDATE statement is executed on the associated table.
  2. The data for the updated rows is stored in two virtual tables: deleted and inserted. The deleted virtual table contains the original data before the update, while the inserted virtual table contains the updated data.
  3. The trigger AFTER UPDATE is executed, and it can access the data in the deleted and inserted virtual tables.
  4. The trigger performs any desired actions, such as logging the changes, validating the data, or updating related tables.
  5. The trigger completes execution, and control returns to the calling application.

It's important to note that the AFTER UPDATE trigger is executed after the data has been successfully updated in the table, so you can use the trigger to perform additional actions based on the updated data. However, it's also possible to perform any necessary validation or data manipulation in the trigger to ensure that the data being updated is valid before it's updated in the table.

In general, the AFTER UPDATE trigger is useful for performing actions that need to be performed after the data has been updated in the table, such as logging, auditing, or data validation. The trigger can also be used to update related tables or perform complex operations on the updated data.

If I perform select query on the relevant table in an after update trigger, which data do I access? Original or updated?

If you perform a SELECT query on the relevant table in an AFTER UPDATE trigger, you will access the updated data. The AFTER UPDATE trigger is executed after the data in the table has been updated, so any SELECT query performed within the trigger will reflect the updated data. If you need to access the original data before the update, you can use the deleted virtual table, which contains the original data.

How is the situation for after delete trigger?

If you perform a SELECT query on the relevant table in an AFTER DELETE trigger, you will access the data that has been deleted from the table. The AFTER DELETE trigger is executed after the data in the table has been deleted, so any SELECT query performed within the trigger will not reflect the deleted data. If you need to access the data that was deleted, you can use the deleted virtual table, which contains the deleted data.

Post a Comment