MS SQL SERVER – Triggers

MS SQL SERVER – Triggers

Today I was working on Triggers for one of my project so thought to share some information about triggers.

What are Triggers?
Trigger is a database object that is attached to a table and gets fired upon action. The actions are INSERT, UPDATE or DELETE.

How to perform an INSERT/UPDATE Trigger
In my case I had two separate tables: claims and claim status history. So when the claim status changes during the claim process then I wanted to store the history process. When a new claim is created there is a INSERT trigger to add a record to claim history table.

CREATE TRIGGER trg_xxx_update
ON [xxx].[xTable]
AFTER INSERT, UPDATE
AS
INSERT INTO [xxx].StatusHistory
(
  [Id],
  [StatusId],
  [CreatedDateTime],
  [CreatedBy],
  [UpdatedDateTime],
  [UpdatedBy]
)
SELECT
  Id,
  StatusId,
  UpdatedDateTime as CreatedDateTime,
  UpdatedBy as CreatedBy,
  UpdatedDateTime,
  UpdatedBy
FROM
  Inserted 

Action Definitions
1. INSERT – perform action after inserting a record to the table
2. UPDATE – perform action after an update is made to the table
3. DELETE – perform action when delete a record from the table

SQL SERVER supports 2 kinds of Triggers
– Data manipulation events (DML triggers) – INSERT/UPDATE/DELETE
– Data definition events (DDL triggers) such as CREATE TABLE

There are two kinds of DML triggers:
1. AFTER
– the trigger fires after the event it is associated with finishes and can only be defined on permanent talbles.
2. INSTEAD OF
– the trigger fires instead of the event it is associated with and can be defined on permanent tables and views

How trigger works
Trigger executes only once no matter how many rows it may affect and also the schema of the trigger must be same of the table or view.
Triggers make use of two special tables called inserted and deleted.
The inserted table contains the data referenced in an INSERT before it is actually comitted to the database.
The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed form the database
When using UPDATE both tables are used as new data referenced is contained in the inserted and the data that is updated is contained in deleted.

Nested After Triggers
Triggers can be nested that is trigger on TableA updates Table B then TableB trigger updates TableC and so on can continue to a maximum of 32 executions.
Nested triggers are on be default on SQL SERVER but you have the option to disable it.
To check the setting:

EXEC sp_configure 'nested triggers';

To stop all nested triggers:

EXEC sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

RECONFIGURE is important to make the setting take place.

Stop trigger for a database:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Restrict Trigger Nesting
This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.

IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Printing Trigger levels, ids

Print 'testing TRIGGER_NESTLEVEL '
+ CAST(TRIGGER_NESTLEVEL() AS VARCHAR(3))
;
print object_id( '[xxx].[trg_xxx_update]' );
print db_id();

INSTEAD OF Triggers
INSTEAD OF Triggers are common with views, the reason is that when you want to UPDATE a view only the base table can be updated at a time. Views may contain aggregations or functions which prevent update therefore An INSTEAD OF trigger can take that UPDATE statement against the view and instead of executing it, replace it with two or more UPDATE statements against the base tables of the view.

How to prevent trigger from recursing
1. Disable trigger recursion
2. Use a trigger INSTEAD OF UPDATE, INSERT
3. Control the trigger by preventing using IF UPDATE

ALTER TRIGGER [dbo].[tblXXX] 
   ON  [dbo].[tblXXX]
   FOR INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    IF UPDATE(Name) BEGIN
        UPDATE tblXXX 
        SET Name = Name + CAST((b.Id) AS VARCHAR(10))
        FROM tblXXX a
            INNER JOIN INSERTED b on a.Id = b.Id
    END
END

Side Effect
ROLLBACK TRAN – read more on this