Triggers Explained in SQL Server

In this blog, I will explain SQL Triggers.

Introduction

 

In this blog, I will explain SQL Triggers. Triggers are stored programs, which are automatically executed or fired when some events occur (insert, update, delete). Triggers are stored in and managed by the DBMS. They are used to maintain the referential integrity of data by changing the data in a systematic fashion. DBMS automatically fires the trigger as a result of a data modification to the associated table. Stored procedures are explicitly executed by invoking a call to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures. Triggers are used in the following events:

• DML-INSERT, UPDATE, DELETE
• DDL-CREATE, ALTER, DROP

Types of DML Triggers

1. Before Triggers
2. After Triggers
3. Instead of Triggers

After Triggers

 

After triggers are invoked after DML (insert, update and delete) operations.

1. After Insert
2. After Update
3. After Delete

After Insert

CREATE TRIGGER TRIGGER_NAME on TB_NAME
FOR INSERT
AS
declare @COLUMN1 int, @COLUMN2 varchar(55), @audit_action varchar(100);

select @COLUMN1 =i.TB_Columnname1 from inserted i;
select @COLUMN2 =i.TB_Columnname2 from inserted i;

set @audit_action='Inserted Record -- After Insert Trigger.';

insert into TB_NAME(TB_Columnname1, TB_Columnname2,Audit_Action)
values(@COLUMN1@COLUMN2);
PRINT 'AFTER INSERT trigger fired.'

After Update

CREATE TRIGGER TRIGGER_NAME ON TB_NAME
FOR UPDATE
AS
Declare @COLUMN1 int, @COLUMN2 varchar(55), @audit_action varchar(100);
select @COLUMN1 =i.TB_Columnname1 from inserted i;
select @COLUMN2 =i.TB_Columnname2 from inserted i;
if update(@COLUMN1)
set @audit_action='Update Record empname --- After Update Trigger.';
if update (@COLUMN2)
set @audit_action='Update Record emp sal --- After Update Trigger.';
insert into TB_NAME(TB_Columnname1, TB_Columnname2,Audit_Action)
values(@COLUMN1@COLUMN2);
PRINT 'AFTER UPDATE trigger fired.'

After Delete

CREATE TRIGGER TRIGGER_NAME ON TB_NAME
FOR DELETE
AS
Declare @COLUMN1 int, @COLUMN2 varchar(55), @audit_action varchar(100);
Select @COLUMN1 =i.TB_Columnname1 FROM deleted d;
Select @COLUMN2 =i.TB_Columnname2 from deleted d;
select @audit_action='Deleted -- After Delete Trigger.';
insert into TB_NAME(TB_Columnname1, TB_Columnname2,Audit_Action)
values(@COLUMN1@COLUMN2);
PRINT 'AFTER DELETE TRIGGER fired.'

Benefits

1. Generating some derived column values automatically.
2. Enforcing referential integrity.
3. Event logging and storing information on table access.
4. Synchronous replication of tables.
5. Imposing security authorizations.