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.

References

See Also