Home > DeveloperSection > Blogs > Triggers in SQL

Triggers in SQL


MSSQL Server .NET  SQL Server 
Ratings:
0 Comment(s)
 675  View(s)
Rate this:

Triggers in SQL

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


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

Follow MindStick