Triggers in SQL Server are powerful tools that help automate specific actions when predefined events occur. They are an efficient way to maintain data integrity and consistency. In this guide, we'll explore the concept of create trigger sql server and provide practical examples.
The concept of triggers is straightforward: they're υ-reactive code snippets that execute SQL statements in response to certain database events. These events include INSERT, UPDATE, DELETE, and ALTER TABLE.
Creating Simple Triggers in SQL Server
Let's start by creating a simple trigger that logs changes to an employee table.
The first step is to create a table to store audit logs:
```sql CREATE TABLE AuditLog ( Operation NVARCHAR(10), TableName NVARCHAR(128), ColumnName NVARCHAR(128), OldValue NVARCHAR(4000), NewValue NVARCHAR(4000), ChangeDate DATETIME ); ```
Trigger on INSERT Operation
Now, let's create a trigger that logs changes when a new record is inserted into the Employees table:
```sql CREATE TRIGGER TR_Employees_Insert ON Employees AFTER INSERT AS BEGIN INSERT INTO AuditLog (Operation, TableName, ColumnName, NewValue, ChangeDate) SELECT 'INSERT', 'Employees', COLUMN_NAME, CAST(INSERTED.* AS NVARCHAR(4000)), GETDATE() FROM inserted CROSS APPLY (SELECT COLUMN_NAME FROM sys.columns WHERE object_id = object_id('Employees') AND is_hidden = 0); END; ```
In this trigger, we're using the INSERTED pseudo-table to access the new values. The trigger inserts a record into the AuditLog table for each column in the Employees table that has been affected.
Trigger on UPDATE Operation
Similarly, you can create a trigger to log updates:
```sql CREATE TRIGGER TR_Employees_Update ON Employees AFTER UPDATE AS BEGIN INSERT INTO AuditLog (Operation, TableName, ColumnName, OldValue, NewValue, ChangeDate) SELECT 'UPDATE', 'Employees', COLUMN_NAME, CAST(DELETED.* AS NVARCHAR(4000)), CAST(INSERTED.* AS NVARCHAR(4000)), GETDATE() FROM deleted, inserted CROSS APPLY (SELECT COLUMN_NAME FROM sys.columns WHERE object_id = object_id('Employees') AND is_hidden = 0); END; ```
In this trigger, we're using both the INSERTED and DELETED pseudo-tables to access the old and new values.
Enabling and Disabling Triggers
Triggers are disabled by default when they're created. To enable the triggers, you can use the following commands:
ENABLE TRIGGER TR_Employees_Insert ON Employees;
ENABLE TRIGGER TR_Employees_Update ON Employees;
To disable a trigger, use the DISABLE command followed by the trigger's name and the table name it's attached to:
Remember, triggers should be used judiciously to maintain performance and avoid unexpected behavior.
As you've seen, creating and using triggers in SQL Server can greatly enhance your application's functionality and data integrity. They allow you to respond to data changes in a controlled and predictable way. Keep exploring and experimenting with triggers to master this powerful feature.