blog

Home / DeveloperSection / Blogs / Triggers in SQL

Triggers in SQL

priyanka kushwaha2760 28-Jan-2015

In this blog, I’m explaining about triggers in SQL

Triggers are database object. Basically these are special  type of store procedure that are automatically fired when DDL  or DML command statement related  with the trigger is executed. Triggers are used to evaluated  data before  or after data modification  using DDL and DML statement.

Basically, triggers are classified into two  main types:

1.      After triggers

2.      Instead of triggers

After Triggers:

These triggers run after an insert,  update or delete  on a table.  They are not supported for view. After trigger  can be classified  further into three  types as:

  1: After Insert trigger

  2: After update trigger

  3: After Delete trigger

First  of all, let’s create  a table .

CREATE TABLE [dbo].[EmployeeDetail](
 [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [DateOfBirth] [nvarchar](50) NULL,
 [FatherName] [nvarchar](50) NULL,
 [AddressLineFirst] [nvarchar](max) NULL,
 [AddressLineSecond] [nvarchar](max) NULL,
 [City] [nvarchar](50) NULL,
 [State] [nvarchar](50) NULL,
 [Country] [nvarchar](50) NULL,
 [PinCode] [nvarchar](50) NULL,
 [PhoneNo] [nvarchar](50) NULL,
 [EmailAddress] [nvarchar](50) NULL,
 [CreationDate] [datetime] NULL,
 [ModificationDate] [datetime] NULL,
 CONSTRAINT [PK_EmployeeDetail] PRIMARY KEY CLUSTERED
(
                [EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[EmployeeDetail]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDetail_EmployeeDetail] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[EmployeeDetail] ([EmployeeID])
GO
 
ALTER TABLE [dbo].[EmployeeDetail] CHECK CONSTRAINT [FK_EmployeeDetail_EmployeeDetail]
GO

 

 1.      After Insert trigger:

This trigger is fired after  an insert  on the table.

Example:

Create trigger [dbo].[trgAfterinsert] on [dbo].[EmployeeDetail]
after insert
as
declare @empid int;
select @empid=i.EmployeeID from inserted i;
update EmployeeDetail set CreationDate=getdate() where EmployeeID=@empid
 2.      After Update trigger

This trigger is fired after  an update  on the table.

Example:

ALTER trigger [dbo].[trgAfterUpdate] on [dbo].[EmployeeDetail]
for UPDATE
as
declare @empid int;
select @empid=i.EmployeeID from deleted i;
update EmployeeDetail set ModificationDate=getdate() where EmployeeID=@empid

 3.    After Delete trigger


This trigger  is fired  after  a deleted on the table.

Example:

ALTER trigger [dbo].[trgAfterUpdate] on [dbo].[EmployeeDetail]
After delete
as
declare @empid int;
select @empid=i.EmployeeID from deleted i;
update EmployeeDetail set ModificationDate=getdate() where EmployeeID=@empid

 

 Instead of triggers:


Instead of triggers can be defined  on either  tables or views.


Instead of triggers are classified into three types:


1. Instead of insert Trigger


2.  Instead of Update Trigger


3.  Instead of Delete Trigger

 

Example:

ALTER trigger [dbo].[trgAfterDelete] on [dbo].[EmployeeDetail]
 instead of delete
 as
declare @id int
declare@FirstName nvarchar(50)
declare@RoleId int
               
select @id=d.id,@FirstName=d.FirstName,@RoleId=d.RoleId from deleted d
 
                                Begin tran  if (@RoleId='Admin')
                                begin
                                 RAISERROR ('Cannot delete your record',16,1);
                                  RollBack;
                                end
else
delete  from EmployeeDetail where id=@id
commit

Updated 28-Jan-2015

Leave Comment

Comments

Liked By