articles

Home / DeveloperSection / Articles / Trigger in SQL

Trigger in SQL

Anchal Kesharwani10343 25-Jun-2014

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');

 

Trigger in SQL

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';

Trigger in SQL

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';

 

Trigger in SQL

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)
)

Trigger in SQL

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.

 

 


Updated 07-Sep-2019

Leave Comment

Comments

Liked By