SQL Triggers

SQL triggers are special types of stored procedures that run automatically when certain events occur on a table or view in the database. SQL triggers can be used to maintain data integrity, enforce business rules, audit changes, or perform other actions based on the event.

There are three main types of SQL triggers: DML triggers, DDL triggers, and logon triggers. DML triggers fire when data is modified by INSERT, UPDATE, or DELETE statements. DDL triggers fire when database objects are created, altered, or dropped by CREATE, ALTER, or DROP statements. Logon triggers fire when a user session is established by a LOGON event.

To create a SQL trigger, you use the CREATE TRIGGER statement with the following syntax:

CREATE TRIGGER trigger_name

ON table_name

[ WITH trigger_option ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

AS

{ sql_statement | EXTERNAL NAME method_name }

The trigger_name is the name of the trigger that you want to create. The table_name is the name of the table or view that the trigger is associated with. The trigger_option is an optional clause that specifies additional options for the trigger, such as ENCRYPTION or EXECUTE AS. The FOR, AFTER, and INSTEAD OF keywords specify when the trigger should fire in relation to the event. The INSERT, UPDATE, and DELETE keywords specify which event should cause the trigger to fire. The sql_statement is a block of Transact-SQL code that defines the logic of the trigger. The EXTERNAL NAME clause allows you to call a method from an assembly created in the .NET Framework common language runtime (CLR) instead of using Transact-SQL code.

Here is an example of a simple DML trigger that inserts a record into an audit table whenever a row is inserted into a customer table:

CREATE TRIGGER trg_customer_insert

ON customer

AFTER INSERT

AS

BEGIN

  INSERT INTO customer_audit (customer_id, action_date, action_type)

  SELECT customer_id, GETDATE(), 'INSERT'

  FROM inserted;

END;