DML Triggers are created against a table or view and are defined for one of three events:

1. INSERT
2. UPDATE
3. DELETE

The general syntax for a CREATE TRIGGER statement is:

CREATE TRIGGER trigger_name
ON [TABLE | VIEW]
[WITH dml_trigger_options]
[FOR | AFTER | INSTEAD OF]
[INSERT | UPDATE | DELETE]
[WITH APPEND]
AS
[sql_statement | EXTERNAL_NAME <method>]

'ALTER TRIGGER' can be used to redefine the trigger.

DML AFTER Triggers

AFTER triggers can only be defined for tables and fire only after the modification passes all constraints

CREATE TABLE testing(
testing_id TIMESTAMP NOT NULL,
text VARCHAR(50))
GO

CREATE TRIGGER tr_dml_insert_robot
ON dbo.Robot
AFTER INSERT
AS
INSERT INTO dbo.testing (text)
VALUES ('Another Robot rolls of the production line')
GO

INSERT INTO Robot (RobotID, OrderID, Model, UnitPrice, ManufactureDate)
VALUES(9, 9, 'DB2-V1', '32.55', GETDATE())
GO

INSERT INTO Robot (RobotID, OrderID, Model, UnitPrice, ManufactureDate)
VALUES(10, 10, 'RVJ-62', '32.55', GETDATE())
GO

SELECT * FROM dbo.testing
GO

The 'FOR' keyword is a synonym for 'AFTER'.

Triggers have access to the 'Inserted' and 'Deleted' tables managed by SQL Server. Thus triggers can be designed to use the data from the statements that trigger them:

ALTER TRIGGER tr_dml_insert_robot
ON dbo.Robot
FOR INSERT
AS
INSERT INTO dbo.testing (text)
SELECT 'New ' + t1.Model + ' robot created on ' + 
	CONVERT(VARCHAR, t1.ManufactureDate)
	FROM Inserted t1

DML INSTEAD OF Triggers

DML 'INSTEAD OF' triggers can be used on tables, for instance to redefine an 'INSERT' statement to act as an 'UPDATE OR INSERT' statement:

CREATE TRIGGER utr_dml_update_or_create_robot
ON Robots
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT t1.serialno FROM Robots t1, inserted t2
	WHERE t1.serialno = t2.serialno)
	UPDATE t1 SET
	t1.serialno = t2.serialno,
	t1.make = t2.make,
	t1.modelno = t2.modelno
	t1.manufacture_date = t2.manufacture_date
	FROM Robots t1, inserted t2
	WHERE t1.serialno = t2.serialno
ELSE
INSERT INTO Robots
	SELECT t1.serialno, t1.make, t1.modelno, t1.manufacture_date
	FROM inserted t1
END

The trigger first checks the if the value to be inserted (stored in the special system table 'inserted') already exists in the target table. If it does the values from the 'inserted' table are used to generate an 'UPDATE' statement. Otherwise an 'INSERT' statement is executed.

INSERTS are not possible on VIEWS that update multiple tables. 'INSTEAD OF' triggers can be defined to handle such updates. Consider the following arrangement of tables and a view:

CREATE TABLE rel_author_book( auth_id INT NOT NULL, book_id INT NOT NULL,
CONSTRAINT pk_author_book PRIMARY KEY (auth_id, book_id))
GO

CREATE TABLE Authors( auth_id INT NOT NULL, fullname VARCHAR(100),
CONSTRAINT pk_authors PRIMARY KEY (auth_id) )
GO

CREATE TABLE Books( book_id INT NOT NULL, title VARCHAR(100),
edition INT, pages INT, publication_date date,
CONSTRAINT pk_books PRIMARY KEY (book_id) )
GO

CREATE VIEW v_book AS
SELECT t2.auth_id, t1.book_id, t2.fullname, t1.title, t1.edition, 
t1.pages, t1.publication_date
FROM Books t1, Authors t2, rel_author_book t3
WHERE t3.auth_id = t2.auth_id AND t3.book_id = t1.book_id
GO

We can load data into the three tables that are used in the VIEW definition using three seperate INSERT statements:

INSERT INTO Authors(auth_id, fullname) VALUES (1, 'Randal L. Schwartz'), 
(2, 'Tom Phoenix'), (3, 'Brian Foy'), (4, 'Tom Christiansen'), (5, 'Larry Wall')
GO

INSERT INTO Books(book_id, title, edition, pages, publication_date)
 VALUES (1, 'Learning Perl', 1, 274, '1993-11-01' ), 
 (2, 'Learning Perl', 2, 300, '1997-07-01'), 
 (3, 'Learning Perl', 3, 336, '2001-07-18'), 
 (4, 'Learning Perl', 4, 312, '2005-07-14')
GO

INSERT INTO rel_author_book(auth_id, book_id)
 VALUES (1, 1), (1,2), (1,3), (1,4), 
 (2,3), (2,4), (3,4),  (4,2), (5,2)
GO

To add details for another book, we could define another three insert statements for the three underlying tables with the appropriate values specified for each table. We cannot directly update the VIEW with insert statements, because the view consists of multiple tables. But we can define a trigger that will fire on the VIEW in place of any insert statements. The trigger can replace the single INSERT statement with three INSERT statements applied to three tables underlying the view:

CREATE TRIGGER tr_dml_v_book_update
ON v_book
INSTEAD OF INSERT
AS
  BEGIN
	IF EXISTS(SELECT t1.auth_id 
		FROM Authors t1, Inserted t2 
		WHERE t2.auth_id = t1.auth_id)
	UPDATE t1 SET t1.fullname = t2.fullname FROM Authors t1, inserted t2 
		WHERE t1.auth_id = t2.auth_id
	ELSE 
	INSERT INTO Authors (auth_id, fullname) SELECT t2.auth_id, t2.fullname 
		FROM Inserted t2

	IF EXISTS(SELECT t1.book_id 
		FROM Books t1, Inserted t2 
		WHERE t1.book_id = t2.book_id)
	UPDATE t1 SET t1.title = t2.title, t1.edition = t2.edition, 
		t1.pages = t2.pages, t1.publication_date = t2.publication_date 
		FROM Books t1, inserted t2 WHERE t1.book_id = t2.book_id
	ELSE
	INSERT INTO Books (book_id, title, edition, pages, publication_date)
		SELECT t2.book_id, t2.title, t2.edition, t2.pages, t2.publication_date 
		FROM Inserted t2

	IF EXISTS (SELECT t1.auth_id, t1.book_id 
		FROM rel_author_book t1, Inserted t2
		WHERE t2.auth_id = t1.auth_id AND t2.book_id = t1.book_id)
		SELECT 1
	ELSE
	INSERT INTO rel_author_book (auth_id, book_id) 
		SELECT t2.auth_id, t2.book_id FROM Inserted t2
  END
GO

With the trigger defined, we can use one insert statement on the view to effect three separate inserts on the underlying tables. Any inserts called on the v_book VIEW, will be handled by the v_book_update trigger:

INSERT INTO v_book VALUES (1, 5, 'Randal Schwartz', 'Learning Perl', 5, 352, '2008-07-15')
INSERT INTO v_book VALUES (2, 5, 'Tom Phoenix', 'Learning Perl', 5, 352, '2008-07-15')
INSERT INTO v_book VALUES (3, 5, 'Brian Foy', 'Learning Perl', 5, 352, '2008-07-15')

DDL Database Triggers

DDL triggers are fired by either DDL statements or when a user logs in to an instance. DDL triggers are defined for event-groups on either a database or for an SQL instance. The general syntax is:

CREATE TRIGGER trigger_name
ON [DATABASE | ALL SERVER]
FOR event_group
AS
  trigger_body

The EVENTDATA function can be used to collect data about an event. EVENTDATA returns data in XML format.

CREATE TABLE audit_table(
id INT IDENTITY NOT NULL,
user_name VARCHAR(50),
command VARCHAR(50),
post_time DATETIME)
GO

CREATE TRIGGER tr_ddl_prevent_drop_table
ON DATABASE
FOR DROP_TABLE
AS 
DECLARE @xml_event_data XML
DECLARE @post_time VARCHAR(50), @event_type VARCHAR(50), @server_name VARCHAR(50), 
@database_name VARCHAR(50), @object_type VARCHAR(50), @object_name VARCHAR(50),
@user_name VARCHAR(50), @command_text VARCHAR(MAX)

SET @xml_event_data = EVENTDATA() 

SELECT @post_time = REPLACE(CONVERT(VARCHAR(50), 
	@xml_event_data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ')
SELECT @event_type = CONVERT(VARCHAR(15), 
	@xml_event_data.query('data(/EVENT_INSTANCE/EventType)'))
SELECT @server_name = CONVERT(VARCHAR(25), 
	@xml_event_data.query('data(/EVENT_INSTANCE/ServerName)'))
SELECT @database_name = CONVERT(VARCHAR(25), 
	@xml_event_data.query('data(/EVENT_INSTANCE/DatabaseName)'))
SELECT @object_type = CONVERT(VARCHAR(25), 
	@xml_event_data.query('data(/EVENT_INSTANCE/ObjectType)'))
SELECT @object_name = CONVERT(VARCHAR(25), 
	@xml_event_data.query('data(/EVENT_INSTANCE/ObjectName)'))
SELECT @user_name = CONVERT(VARCHAR(15), 
	@xml_event_data.query('data(/EVENT_INSTANCE/UserName)'))
SELECT @command_text = CONVERT(VARCHAR(MAX), 
	@xml_event_data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
  
  PRINT 'Cannot drop tables on ' + @database_name
  ROLLBACK
  INSERT INTO audit_table (user_name, command, post_time) 
  SELECT @user_name, @command_text, @post_time
  GO

The above trigger will fire to prevent a table being dropped from the current database.

DDL Instance Triggers

DDL triggers will also fire on login events. For example, to restrict the number of concurrent users to 5:

CREATE TRIGGER tr_ddl_limit_concurrent_connections
ON ALL SERVER
AS
  BEGIN
	IF (SELECT COUNT(*) FROM sys.dm_exec_sessions 
		WHERE is_user_process = 1 
		AND login_name = suser_sname()) > 5
	PRINT 'No more logins allowed!'
	ROLLBACK
  END