Adventures in Machine Learning

Mastering the SQL Server CREATE TRIGGER Statement: Logging Table Changes

SQL Server CREATE TRIGGER statement: Everything You Need to Know

Have you ever had a scenario where you needed to capture changes made to a table in SQL Server? Perhaps you wanted to track who made insertions, updates or deletions, when the changes were made, and what data was changed.

This is where the SQL Server CREATE TRIGGER statement comes in handy. In this article, we’ll be providing an overview of this statement, its syntax, and how it can be used to create triggers that log changes made to a table.

The Purpose of the CREATE TRIGGER Statement

The CREATE TRIGGER statement is used to create a trigger, which is a database feature that captures changes made to a table. Triggers are event-driven and execute automatically in response to an event in the database, such as data manipulation language (DML) operations like INSERT, UPDATE, or DELETE.

The CREATE TRIGGER statement enables you to define a trigger to execute a set of SQL statements when a specific event occurs in the table.

Syntax of the CREATE TRIGGER Statement

To create a trigger for SQL Server, you must use the CREATE TRIGGER statement. Here’s the syntax for the CREATE TRIGGER statement:


CREATE TRIGGER schema_name.trigger_name
ON schema_name.table_name
[AFTER|INSTEAD OF] event [NOT FOR REPLICATION]
AS
BEGIN
sql_statements
END;

Let’s break this down into smaller bits:

  • The CREATE TRIGGER statement starts with the trigger schema name, followed by the trigger name.
  • The statement declares the name of the source table by using the schema name and the table name.
  • The event specifies the customer action upon which the trigger runs. Commonly used events are INSERT, UPDATE, and DELETE.
  • You can specify an AFTER or INSTEAD OF clause to determine the execution time of the trigger. AFTER is the default trigger, and it gets executed after the event; INSTEAD OF fires the trigger before the SQL statement executes.
  • You can choose to ignore replicated events by using a NOT FOR REPLICATION clause.
  • The sql_statements consist of any valid SQL statements that you want to execute whenever the trigger code is activated.

Virtual Tables for Triggers: INSERTED and DELETED

Whenever a trigger executes, two virtual tables are created: INSERTED and DELETED. These tables store the existing and updated records, which can be used to derive the data changes made.

INSERTED is created for INSERT and UPDATE triggers, storing the newly added or updated records, while DELETED is created for DELETE triggers, storing the deleted records. The pseudo-tables are exclusively available during the trigger execution time and last throughout their duration.

SQL Server CREATE TRIGGER Example

Here’s an example that demonstrates how to use the CREATE TRIGGER statement to create a trigger for logging changes made to a table:

Creating A Table for Logging Changes

To create a table that logs changes made to another table, follow these steps:


CREATE TABLE production.product_audits
(
AuditID int IDENTITY(1,1) PRIMARY KEY,
Audit_Date datetime,
Audit_User varchar(50),
ProductID int,
Price money,
StockLevel smallint
);

After running the query above, a new table named “production.product_audits” will be created. This table will store the changes made to the product table.

Creating an After DML Trigger

Next, we’ll create the trigger with the following statement:


CREATE TRIGGER trg_audit_changes
ON production.products
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
RETURN;
INSERT INTO production.product_audits (Audit_Date, Audit_User, ProductID, Price, StockLevel)
SELECT GETDATE(), SUSER_SNAME(), ISNULL(i.ProductID, d.ProductID), ISNULL(i.Price, d.Price), ISNULL(i.StockLevel, d.StockLevel)
FROM inserted i FULL OUTER JOIN deleted d ON i.ProductID = d.ProductID
WHERE i.ProductID IS NULL OR d.ProductID IS NULL OR (i.Price <> d.Price OR i.StockLevel <> d.StockLevel);
END;

Let’s briefly explain what this means. We first declare the trigger name and table followed by the events that would activate it (AFTER INSERT, UPDATE, and DELETE).

We also set the NOT FOR REPLICATION clause (optional). We then implement the trigger behavior through its SQL statements.

We set NOCOUNT ON to exclude the number of affected rows by the output. The IF NOT EXISTS checks if any rows are affected.

If not, the trigger exits immediately without executing any SQL statement. If the trigger continues, it inserts the time and username of the user performing the change and other columns (ProductID, Price, and StockLevel) using the INSERT INTO statement.

We record the changes made in the INSERTEED and DELETED tables.

Testing the Trigger

Now that we’ve created the trigger, let’s test it. We can execute SQL INSERT, UPDATE, and DELETE statements to show the trigger in action.

We can execute the following SELECT statement to see the changes made to our table:


SELECT * FROM production.product_audits;

This query will retrieve all the rows that our trigger added to the “production.product_audits” table, and they should include the date and time, the user’s name, the product ID, price, and stock level.

Conclusion

The SQL Server CREATE TRIGGER statement is a powerful tool that makes it possible to capture changes made to a table in your database. This statement can be used to create triggers that automatically run when DML operations are performed in a table.

With the virtual tables at your disposal, you can easily keep track of the changes made to your tables. By following the examples provided above, you can create and test your own triggers and start improving the functionality and security of your SQL Server.

In summary, the SQL Server CREATE TRIGGER statement is a powerful tool that enables you to capture changes made to a table. The statement’s syntax allows you to create triggers that automatically run when DML operations are performed, with the virtual tables at your disposal.

In the example provided, we’ve shown how to log changes made to a table, as well as test the trigger. By using CREATE TRIGGER statements in your SQL Server database, you can improve functionality and security.

The importance of this topic lies in the need to track data changes and ensure data integrity, which is crucial for any database-driven application. By mastering this statement, you can create more efficient and secure databases.

Popular Posts