In this article describe the concept of trigger in sql. Trigger is a precompiled object stored in the database that automatically executes when an event occurs in the database server. Triggers are two type before trigger and after trigger. Here, we give the simple example of sql trigger.

Trigger is a precompiled object stored in the database that automatically executes when an event occurs in the database server. The user cannot explicitly execute triggers. Trigger is block of  sql statements . It is also known as it’s a special kind of stored procedure. Triggers are used to ensure data integrity before or after performing data manipulations.

Types of triggers

•    DML trigger
•    DDL trigger
•    Logon trigger

DML trigger

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger.

There are two types of DML trigger:

•     AFTER trigger
•     Instead of trigger

AFTER trigger

After trigger is fired after, row is manipulation done(either insert/delete/update).

There after trigger are classified into three categories:

·         AFTER INSERT trigger

·         AFTER UPDATE trigger

·         AFTER DELETE trigger

AFTER INSERT trigger

The after insert trigger is fired on insert the record.

Syntax

CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE_NAME> <AFTER | FOR> INSERT
AS
       -------HERE THE BODY OF TRIGGER
GO

Example

CREATE TRIGGER TEST_INSERT
ON Customer AFTER INSERT
AS
       print 'New customer record inserted'
GO
 
INSERT INTO Customer(custFistName, custLastName, custAdress) VALUES('AJAY','KUMAR','ALLAHABAD');

AFTER UPDATE trigger

The after update trigger is fired on insert the record.

Syntax

CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE_NAME> <AFTER | FOR> UPDATE
AS
       -------HERE THE BODY OF TRIGGER
GO

Example

CREATE TRIGGER TEST_AFTER_UPDATE
ON Customer FOR UPDATE
AS
       print 'Customer record was updated'
GO
 
UPDATE Customer SET
custFistName='SATYA' WHERE custAdress='ALLAHABAD';

AFTER DELETE trigger

Syntax

CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE_NAME> <AFTER | FOR> DELETE
AS
       -------HERE THE BODY OF TRIGGER
GO

Example

CREATE TRIGGER TEST_AFTER_DELETE
ON Customer FOR DELETE
AS
       print 'Customer record was deleted'
GO
 
DELETE FROM Customer where custAdress='ALLAHABAD';

Instead of trigger

Instead of trigger is fired before actual manipulation happen. Like AFTER triggers, each Instead of trigger gives you access to two virtual tables called Inserted and Deleted. For a DELETE trigger, the virtual table Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version.

Syntax

CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE_NAME> <INSTEAD OF> INSERT | UPDATE | DELETE
AS
       -------HERE THE BODY OF TRIGGER
GO

There are three type of instead of trigger:

·         Instead of INSERT trigger

·         Instead of UPDATE trigger

·         Instead of DELETE trigger

Instead of INSERT trigger

Instead of INSERT trigger fired when insert action perform.

Example

CREATE TABLE Employee
(
 EmpID int identity,
 EmpName varchar(50),
 EmpSal decimal (10,2)
)
 
create table Employee_Audit
(
 EmpID int,
 EmpName varchar(50),
 EmpSal decimal(10,2),
 AuditAction varchar(100),
 AuditTimestamp datetime
)
 
 
 
CREATE TRIGGER trgInsteadOfInsert ON Employee
INSTEAD OF Insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.EmpID from inserted i;
select @emp_name=i.EmpName from inserted i;
select @emp_sal=i.EmpSal from inserted i;
SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
BEGIN
 BEGIN TRAN
 SET NOCOUNT ON
 if(@emp_sal<=5000)
 begin
 RAISERROR('Cannot Insert where salary > 5000',16,1); ROLLBACK; end
 else begin Insert into Employee (EmpName,EmpSal) values (@emp_name,@emp_sal); Insert intoEmployee_Audit(EmpID,EmpName,EmpSal,AuditAction,AuditTimestamp)values(@@identity,@emp_name,@emp_sal,@audit_action,CURRENT_TIMESTAMP);
 COMMIT;
 PRINT 'Record Inserted -- Instead Of Insert Trigger.'
END
END

Insert record:

insert into Employee(EmpName,EmpSal)values ('Ramesh',8000);

Instead of UPDATE trigger

Example

CREATE TRIGGER trgInsteadOfUpdate ON Employee
INSTEAD OF Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.EmpID from inserted i;
select @emp_name=i.EmpName from inserted i;
select @emp_sal=i.EmpSal from inserted i;
SET @audit_action='Inserted Record -- Instead Of Update Trigger.';
BEGIN
 BEGIN TRAN
 SET NOCOUNT ON
 if(@emp_sal>=10000)
 begin
 RAISERROR('Cannot Insert where salary < 10000',16,1); ROLLBACK; end
 else begin Insert into Employee_Audit(EmpID,EmpName,EmpSal,AuditAction,AuditTimestamp)values(@@identity,@emp_name,@emp_sal,@audit_action,CURRENT_TIMESTAMP);
 COMMIT;
 PRINT 'Record Updated -- Instead Of Update Trigger.'
END
END

Update record:

update Employee set EmpSal = 15000 where EmpID = 7
update Employee set EmpSal = 5000 where EmpID = 2

Instead of DELETE trigger

Example

CREATE TRIGGER trgInsteadOfDelete ON Employee
INSTEAD OF Delete
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=d.EmpID from deleted d;
select @emp_name=d.EmpName from deleted d;
select @emp_sal=d.EmpSal from deleted d;
SET @audit_action='Deleted Record -- Instead Of Delete Trigger.';
BEGIN
 BEGIN TRAN
 SET NOCOUNT ON
 if(@emp_sal>=15000)
 begin
 RAISERROR('Cannot Insert where salary < 15000',16,1); ROLLBACK; end
 else begin Delete FROM Employee where EmpID=@emp_id
 COMMIT;
 PRINT 'Record Deleted -- Instead Of Delete Trigger.'
END
END

Delete record:

Delete FROM Employee where EmpID=8
Delete FROM Employee where EmpID=2

DDL trigger

DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as create, alter, and drop table in database operations.

Syntax for create, alter, and drop table

CREATE TRIGGER <TRIGGER_NAME>
ON Database FOR [CREATE_TABLE | ALTER_TABLE | DROP_TABLE]
AS
       -------HERE THE BODY OF TRIGGER
GO

Example

CREATE TRIGGER trgCreateTable
ON Database FOR CREATE_TABLE
AS
       PRINT 'TRIGGER FIRED ON CREATE TABLE IN DATABASE'
GO
 
CREATE TABLE BOKK_TITLE
(
       book_id int primary key identity,
       book_name varchar(100),
       book_title varchar(100),
       book_price float,
       book_descrption varchar(100)
)

As this example all we understand create, alter, and drop table.

Logon trigger

In the logon trigger raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.

Syntax

CREATE TRIGGER <TRIGGER_NAME>
 
ON ALL SERVER FOR LOGON
 
AS
 
--- here the nody of trigger
 
GO

Example

create trigger evil_trigger
on all server
for logon
as
begin
       select * from evil_table;
end

Benefits of triggers

Triggers can be written for the following purposes:

·         Generating some derived column values automatically

·         Enforcing referential integrity

·         Event logging and storing information on table access

·         Auditing

·         Synchronous replication of tables

·         Imposing security authorizations

·         Preventing invalid transactions

Drawback of Triggers 

·     It is easy to view table relationships, constraints, indexes, stored procedure in

database but triggers are difficult to view.

·     Triggers execute invisible to client-application application. They are not visible or

can be traced in debugging code.

·     It is hard to follow their logic as it they can be fired before or after the database

insert/update happens.

·     It is easy to forget about triggers and if there is no documentation it will be

difficult to figure out for new developers for their existence.

·         Triggers run every time when the database fields are updated and it is

overhead on system. It makes system run slower.