Home > DeveloperSection > Articles > Trigger in SQL

Trigger in SQL


ASP.Net ASP.Net 
Ratings:
0 Comment(s)
 5866  View(s)
Rate this:

Trigger in SQL

 

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.

 

 


Don't want to miss updates? Please click the below button!

Follow MindStick