SQL triggers are special types of stored procedures that are automatically executed in response to specific events or actions that occur in a database. Triggers can be defined to execute in response to events such as inserts, updates, or deletes on tables, and can be used to enforce data integrity constraints, perform complex data validation, or initiate business logic processes.
There are two types of SQL triggers: DML (Data Manipulation Language) triggers and DDL (Data Definition Language) triggers. DML triggers are fired in response to DML events such as INSERT, UPDATE, or DELETE statements, while DDL triggers are fired in response to DDL events such as CREATE, ALTER, or DROP statements.
Triggers are defined using the CREATE TRIGGER statement, which specifies the trigger name, the event that fires the trigger, the table or view to which the trigger is attached, and the Transact-SQL statements to execute when the trigger fires.
Triggers can be either “after triggers” or “instead of triggers.” After triggers execute after the DML or DDL operation has completed, while instead of triggers execute instead of the DML or DDL operation. Instead of triggers are often used to implement complex view update logic or to enable an application to perform an operation that is normally not allowed.
The syntax for creating a SQL trigger in SQL Server database is as follows:
CREATE TRIGGER [Trigger_Name] ON [Table_Name] [AFTER/INSTEAD OF] [Trigger_Event] AS BEGIN [Trigger_Code] END
[Trigger_Name]: The name given to the trigger.
[Table_Name]: The name of the table on which the trigger will be created.
[Trigger_Event]: The event that triggers the execution of the trigger code. This can be either AFTER or INSTEAD OF an event such as INSERT, UPDATE, or DELETE.
[Trigger_Code]: The SQL statements that will be executed when the trigger is fired.
Here is an example of a SQL trigger that logs any changes made to a specific table in SQL Server database:
CREATE TRIGGER [dbo].[AuditChanges] ON [dbo].[Customers] AFTER INSERT, UPDATE, DELETE AS BEGIN IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN INSERT INTO [dbo].[AuditTrail] (TableName, Operation, PrimaryKey, OldValue, NewValue) SELECT 'Customers', CASE WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'UPDATE' WHEN EXISTS(SELECT * FROM inserted) THEN 'INSERT' WHEN EXISTS(SELECT * FROM deleted) THEN 'DELETE' END, CAST(ISNULL(inserted.CustomerID, deleted.CustomerID) AS NVARCHAR(MAX)), CAST(ISNULL((SELECT * FROM deleted FOR XML RAW), '') AS NVARCHAR(MAX)), CAST(ISNULL((SELECT * FROM inserted FOR XML RAW), '') AS NVARCHAR(MAX)) FROM inserted FULL OUTER JOIN deleted ON inserted.CustomerID = deleted.CustomerID END END
In this example, the trigger is named AuditChanges and is created on the Customers table. The trigger is set to fire AFTER any INSERT, UPDATE, or DELETE operation. The trigger code then logs the details of the changes made to the AuditTrail table.
Note that the trigger code uses the inserted and deleted tables to retrieve the data before and after the change. The code also uses a FULL OUTER JOIN to ensure that all rows are included in the audit trail, even if only one of the inserted or deleted tables has data.
SQL triggers are used for enforcing data integrity and implementing complex business logic processes. However, they can also be a source of performance problems if they are not designed and implemented carefully. Therefore, it is important to use triggers judiciously and to test them thoroughly before deploying them in a production environment.