T-SQL Tutorial

T-SQL Triggers


In this article we will learn how to use triggers in the Transact SQL server. A trigger is an object created in the database that is automatically triggered when an event occurs. Events such as: insert, update, delete.
The triggers are of several types: DML triggers, DDL triggers, LOGON triggers.
The most commonly used trigger is the DML type, and is created when using CREATE TRIGGER, followed by the syntax ON {table_name | view_name} FOR INSERT, UPDATE, DELETE.
The ALTER TRIGGER syntax is used to modify the SQL statement inside a trigger.
In SQL Server database triggers can have status enabled or disabled.
The DROP TRIGGER syntax is used to delete a trigger from the database.

Create Trigger

The T-SQL statement Create Trigger is used to create triggers: DML, DDL, Logon.

DML Trigger

CREATE TRIGGER trig_name
ON { table_name | view_name }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { SQL statement [ ; ] }
GO


DDL Trigger

CREATE TRIGGER trig_name
ON { ALL SERVER | DATABASE }
{ FOR | AFTER }
{ CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS }
AS { SQL statement [ ; ] }
GO


Logon Trigger

CREATE TRIGGER trig_name
ON ALL SERVER
{ FOR| AFTER }
LOGON
AS { SQL statement [ ; ] }
GO


Alter Trigger

The Alter Trigger command is used to modify triggers: DML, DDL, Logon.

ALTER TRIGGER trig_name
ON { table_name | view_name }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { SQL statement [ ; ] }
GO


Drop Trigger

To drop or delete a trigger uses the syntax Drop Trigger. The syntax is used for all types of triggers.

DROP TRIGGER trigger_name;
GO


Enable Trigger

To enable a trigger uses the syntax Enable Trigger. The syntax is used to enable one or all triggers.

ENABLE TRIGGER trigger_name ON table_name;
ALTER TABLE table_name ENABLE TRIGGER ALL;
ENABLE TRIGGER ALL ON ALL SERVER;


Disable Trigger

To disable a trigger uses the syntax Disable Trigger. The syntax is used to disable one or all triggers.

DISABLE TRIGGER trigger_name ON table_name;
ALTER TABLE table_name DISABLE TRIGGER ALL;
DISABLE TRIGGER ALL ON ALL SERVER;


Trigger example

USE model;
GO
create table courses_log(
id int, name varchar(300), price int,
log_date date, event_type varchar(100)
);
GO

CREATE TRIGGER trg_courses_log ON courses
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ins INT
DECLARE @del INT
DECLARE @id INT
DECLARE @name VARCHAR(300)
DECLARE @price INT
DECLARE @Action VARCHAR(50)
SELECT @ins = i.id, @id = i.id, @name = i.name, @price = i.price FROM INSERTED i
SELECT @del = d.id, @id = d.id, @name = d.name, @price = d.price FROM DELETED d
SET @Action = CASE
WHEN @del IS NOT NULL THEN 'Delete'
WHEN @ins IS NOT NULL THEN 'Insert'
ELSE 'Update'
END
SET @Action = CASE WHEN @del IS NOT NULL THEN
CASE WHEN @ins IS NOT NULL THEN 'Update' ELSE 'Delete' END
WHEN @ins IS NOT NULL THEN 'Insert'
ELSE ''
END
INSERT INTO courses_log(id, name, price, log_date, event_type)
values (@id, @name, @price, GETDATE(), @Action)
END
GO


Triggers in Object Catalog Views

To check a trigger uses T-SQL Object Catalog Views.

select * from sys.triggers;
select * from sys.trigger_events;
select * from sys.trigger_event_types;
select * from sys.objects where type='TR' and type_desc='SQL_TRIGGER';
select * from sys.objects where type='TA' and type_desc='CLR_TRIGGER';