SQL triggers are powerful database features that automate specified actions when specific events occur. Common events include INSERT, UPDATE, and DELETE operations on a table. Today, we'll be exploring practical examples of creating SQL triggers on INSERT and UPDATE events, focusing on the Microsoft SQL Server (MSSQL) syntax.
Before we dive into the examples, let's briefly understand why we use triggers. In simple terms, triggers help us enforce complex business rules, maintain data integrity, and automate tasks that would otherwise require manual processing.
INSERT Triggers
INSERT triggers fire when a new row is inserted into a table. They are beneficial for updating related tables, checking for duplicate values, or performing calculations and aggregations based on the inserted data.
Here's a simple example of an INSERT trigger in MSSQL. This trigger updates a 'LastActivity' field in the 'Customers' table whenever a new row is inserted into the 'Orders' table:
```sql CREATE TRIGGER tr_Orders_AfterInsert ON Orders AFTER INSERT AS BEGIN UPDATE Customers SET LastActivity = GETDATE() WHERE CustomerID IN (SELECT CustomerID FROM inserted); END; ```
Inserted Virtual Table
The inserted system table is a virtual table containing the rows that were inserted and can be used within the trigger to access the newly inserted data. As shown in our example, it's useful for checking specific conditions or updating related rows.
Let's consider another example. Suppose we have an 'Employees' table with an 'EmployeeID' column. We want to enforce a unique constraint on the 'Email' column and throw an error message if a duplicate email is inserted. Here's how we can do it:
```sql CREATE TRIGGER tr_Employees_BeforeInsert ON Employees BEFORE INSERT AS BEGIN IF EXISTS (SELECT 1 FROM Employees WHERE Email = (SELECT Email FROM inserted)) BEGIN RAISERROR('Duplicate email not allowed.', 16, 1); ROLLBACK; END END; ```
Inserted or Deleted Virtual Table
In certain cases, you might want to check the old and new values when a row is updated. Here, we can use both the inserted and deleted virtual tables. For instance, you want to keep track of the old and new values of 'Salary' for each 'Employee' when the row is updated:
```sql CREATE TRIGGER tr_Employees_AfterUpdate ON Employees AFTER UPDATE AS BEGIN INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary) SELECT EmployeeID, (SELECT Salary FROM deleted), (SELECT Salary FROM inserted) WHERE EXISTS (SELECT 1 FROM deleted WHERE Salary != (SELECT Salary FROM inserted)); END; ```
UPDATE Triggers
UPDATE triggers fire when one or more existing rows are updated in a table. They are handy for maintaining referential integrity, updating related tables, or logging changes.
Let's consider an 'Orders' and 'OrderDetails' table scenario. Whenever an 'Order' status is updated, we want to update the 'IsShipped' field in the 'OrderDetails' table based on the new 'OrderStatus' value:
```sql CREATE TRIGGER tr_Orders_AfterUpdate ON Orders AFTER UPDATE AS BEGIN UPDATE OrderDetails SET IsShipped = CASE WHEN OrderStatus = 'Shipped' THEN 1 ELSE 0 END WHERE OrderID = (SELECT OrderID FROM inserted); END; ```
As shown in the examples, both INSERT and UPDATE triggers can streamline database management, enhance data integrity, and automate complex business rules. It's crucial to thoroughly test triggers to prevent unintended side effects and performance issues. Happy coding!
