Triggers in Sql Server:
Triggers are automatically fired when an action occurred like INSERT, UPDATE or DELETE (DML) is performed on a table/view in database.
Triggers are associated with the Table or View directly (Each Table/View has its own Triggers).
Types of Triggers:
There are two types of Triggers, they are
1. After(For) Triggers
2. Instead of Triggers
1. After Triggers:
After Triggers are executed after an action such as INSERT, UPDATE or DELETE is performed.
Types of After Triggers:
- Insert Trigger
- Update Trigger
- Delete Trigger
> Insert Trigger:
Whenever a row is inserted in the target table, the associated trigger will be executed.
The newly inserted record is available in the 'INSERTED' table.
Ex: Syntax
CREATE TRIGGER [TriggerName]
SELECT @Id = i.Id from INSERTED i
INSERT INTO TriggerLogTable VALUES(@Id, 'Insert')
We are fetching the Id of the inserted record and the fetched value is inserted in the TriggerLogTable table.
> Update Trigger:
Whenever a row is updated in the target table, the associated trigger will be executed.
The updated record is available in the 'INSERTED' table.
To find which column is updated, we need to use UPDATE() function and pass the column name of the table to it.
The UPDATE() function will return TRUE for a column if its values was updated else it will return FALSE.
Ex: Syntax
CREATE TRIGGER [TriggerName]
ON [TableName]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Id INT
DECLARE @Action VARCHAR(50)
SELECT @Id = i.Id FROM INSERTED i
IF UPDATE(Name) -- Name is Column in our Target Table
BEGIN
SET @Action = 'Updated Name Column'
END
IF UPDATE(Address) -- Address is Column in our Target Table
BEGIN
SET @Action = 'Updated Address Column'
END
INSERT INTO TriggerLogTable VALUES(@Id, @Action)
END
We are fetching the Id and Action of the Updated record based on the conditions then inserted in the TriggerLogTable table.
> Delete Trigger:
Whenever a row is deleted in the target table, the associated trigger will be executed.
The deleted record is available in the 'DELETED' table.
Ex: Syntax
CREATE TRIGGER [TriggerName]
ON [TableName]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Id int
SELECT @Id = d.Id from DELETED d
INSERT INTO TriggerLogTable VALUES(@Id, 'Deleted')
END
We are fetching the Id of the deleted record and the fetched value is inserted in the TriggerLogTable table.
2. Instead of Triggers
Instead of Triggers are executed instead of any of the INSERT, UPDATE or DELETE operations.
Which means to skip the INSERT/UPDATE/DELETE statement on a table.
Types of INSTEAD OF Triggers:
- INSTEAD OF INSERT Trigger
- INSTEAD OF UPDATE Trigger
- INSTEAD OF DELETE Trigger
For Example an INSTEAD OF DELETE Trigger,
Whenever anyone tries to delete a row from the target table the associated trigger will be executed.
Ex: Syntax
CREATE TRIGGER [TriggerName]
ON [TableName]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Id int
SELECT @Id = d.Id from DELETED d
IF @Id = 5 -- Any condition we can check here based on our requirement
BEGIN
RAISERROR('Rajkumar''s record cannot be deleted',16 ,1)
ROLLBACK
INSERT INTO TriggerLogTable VALUES(@Id, 'Record cannot be deleted.')
END
ELSE
BEGIN
DELETE FROM TableName WHERE Id = @Id
INSERT INTO TriggerLogTable VALUES(@Id, 'Instead Of Delete done')
END
END
Any condition we can check before delete operation, if sensitive data or admin records which can't delete by accidentally or some times the user didn't have privileges to delete records from that table then it's suitable to fire this trigger and log the information who tried to did this delete operation at any time.
INSTEAD OF DELETE triggers cannot be defined on a table that has a foreign key defined with a DELETE action.
Same approach for the INSTEAD OF INSERT Triggers and INSTEAD OF UPDATE Triggers.
Thanks
Rajkumar
