SQL Server Triggers T-SQL

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';

T-SQL Tutorial

T-SQL Triggers