T-SQL Understanding Triggers
Introduction
A trigger is a special kind of stored procedure, which fired automatically, when an event occurs. These events can be further classified into: DML, DDL, and Logon. In this article we will focus on the functionality of a Triggers.
A DML trigger fires when a user tries to modify data through data manipulation language (DML) events. DML events are performing INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any of the above mentioned valid events happen, regardless of whether or not any table rows are affected. The definition of a DML trigger can consists of any logic required or needed by user. Whenever a Trigger is created on a Table or View, two non physical temporary tables are created termed as inserted & deleted. These tables stores the result SET of the event that has occurred on the object(Table or a View). You can easily identify which of these table store what kind of information(kind of inferred from the names, isn't it?)
Check DML Triggers
A DDL trigger fire in response to a variety of DDL events. Events majorly correspond to T-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
A Logon trigger fire in response to the LOGON event that is raised when a user sessions is being established.
For more detailed information check Create Triggers
Usage
Trigger can be used in many ways. In this simple example we'll see how to create a trigger and use it. We'll also learn to create an extremely simple and shorter form of CDC.
Sample Object Creation
CREATE TABLE ForumArticle_MasterTable(
ColumnA SMALLINT PRIMARY KEY,
ColumnB VARCHAR(50) NOT NULL,
ColumnC VARCHAR(50) NOT NULL
)
GO
CREATE TABLE ForumArticle_TriggerResults(
ID SMALLINT IDENTITY PRIMARY KEY,
TableName VARCHAR(100),
PrimaryKeyValue SMALLINT NOT NULL,
Operation VARCHAR(20) NOT NULL,
OperationDate DATETIME NOT NULL DEFAULT GETDATE()
)
GO
Trigger DDL
CREATE TRIGGER ForumArticle_Trigger
ON ForumArticle_MasterTable FOR INSERT,UPDATE,DELETE
AS
BEGIN TRAN TransactionLevelOne
IF(SELECT COUNT(*) FROM deleted)=0
BEGIN
INSERT ForumArticle_TriggerResults
SELECT 'ForumArticle_MasterTable',ColumnA,'INSERT',GETDATE()
FROM inserted
END
IF(SELECT COUNT(*) FROM inserted)=0
BEGIN
INSERT ForumArticle_TriggerResults
SELECT 'ForumArticle_MasterTable',ColumnA,'DELETE',GETDATE()
FROM deleted
END
IF (COLUMNS_UPDATED() & 6) > 0 AND (SELECT COUNT(*) FROM inserted)<>0 AND (SELECT COUNT(*) FROM deleted)<>0
BEGIN
INSERT ForumArticle_TriggerResults
SELECT 'ForumArticle_MasterTable',ColumnA,'UPDATE',GETDATE()
FROM inserted
END
IF @@ERROR<>0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
GO
Test Scripts
--INSERT
INSERT ForumArticle_MasterTable
SELECT 1,'Himanshu','10000'UNION
SELECT 2,'Loves','20000' UNION
SELECT 3,'Trance','20000'
GO
We have created a DML Trigger on ForumArticle_MasterTable. Thus whenever any DML statement on this object happens, our trigger will fire and execute whats written inside its definition. In this case we have inserted three rows. Thus the trigger will execute the first part inside it.
Let's examine the result.
SELECT * FROM ForumArticle_TriggerResults
GO
Similarly we can track other operations too.
--DELETE
DELETE ForumArticle_MasterTable
WHERE ColumnA=1
SELECT * FROM ForumArticle_TriggerResults
--UPDATE
UPDATE ForumArticle_MasterTable
WHERE ColumnA=2
SELECT * FROM ForumArticle_TriggerResults
Note:Could not attach an image due to network issues
Conclusion
We have seen that a trigger works like a stored procedure. You can add any amount of code (Complex scripts are not preferable) inside its definition. The most beautiful part is that it invokes(fires) itself without any supervision or schedule. This makes it an extremely powerful concept in T-SQL. Using a trigger, many complex requirements can be solved. In addition to this, the two sweet children "inserted" & "deleted" add more importance and usefulness.