CREATE TRIGGER

The CREATE TRIGGER statement in SQL Server is used to create a database object that automatically executes a set of SQL statements when a specific event occurs in the database. This event can be an INSERT, UPDATE, or DELETE operation on a table or view.

Syntax

The syntax of the CREATE TRIGGER statement in SQL Server is as follows:

CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT, UPDATE, DELETE}
AS
BEGIN
    -- SQL statements to be executed
END

Here, trigger_name is the name of the trigger that you want to create, table_name is the name of the table or view on which the trigger is created, and {INSERT, UPDATE, DELETE} is the event that triggers the execution of the SQL statements in the trigger. The BEGIN and END keywords enclose the set of SQL statements that are executed when the trigger is triggered.

Example

Let’s take an example of how to create a trigger in SQL Server. Suppose we have a Customers table that stores the details of customers and we want to keep track of any changes made to the Orders table. We can create a trigger named trgOrdersAudit as follows:

CREATE TRIGGER trgOrdersAudit
ON Orders
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    -- Insert a new record into the OrderAudit table
    INSERT INTO OrderAudit (OrderID, CustomerID, OrderDate, ModifiedBy, ModifiedDate, Action)
    SELECT i.OrderID, i.CustomerID, i.OrderDate, USER_NAME(), GETDATE(),
    CASE 
        WHEN EXISTS(SELECT * FROM deleted) AND EXISTS(SELECT * FROM inserted) THEN 'UPDATE'
        WHEN EXISTS(SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted) THEN 'DELETE'
        ELSE 'INSERT'
    END AS Action
    FROM inserted i FULL OUTER JOIN deleted d ON i.OrderID = d.OrderID
END

In this example, we are creating a trigger named trgOrdersAudit on the Orders table. The trigger is set to execute for INSERT, UPDATE, and DELETE events on the table. When the trigger is fired, it inserts a new record into the OrderAudit table that stores information about the changes made to the Orders table. The USER_NAME() and GETDATE() functions are used to capture the username and timestamp of the user who made the change. The CASE statement is used to determine the type of action performed (insert, update, or delete) on the Orders table.

Overall, the SQL CREATE TRIGGER statement allows you to automate tasks based on events that occur in your database. With the right set of SQL statements, you can use triggers to enforce business rules, audit changes, and more.