SQL Server Triggers

Triggers can be defined as a Procedural code that gets executed automatically
upon the execution of events like insertion, updation and deletion of Database Table
or View. Triggers cannot be called or executed directly like Stored Procedures but
instead works automatically on database operations like insert,update and delete.

Suppose there is a case by which we want to save log of a table into a history table
upon insert,update and delete actions, we can create a trigger under the table and
write code inside the trigger to save the records to history table.

There are generally two types of Triggers named as (1) After Triggers or For Triggers
and (2) Instead Of Triggers.

After Triggers :

After Triggers are triggers that gets executed only after the operation that fired it

runs successfully. This trigger is not supported for Views.There are three types of 

After Triggers :

1. After Insert Trigger

2. After Update Trigger

3. After Delete Trigger

After Insert Trigger  runs after the execution of the insert operation. If in case

insertion fails, this trigger won't get executed.

After Update Trigger is a trigger that executes after an update operation.

After Delete Trigger is a trigger that executes after a delete operation.

I will explain with examples below. Suppose there is a Product table and a Product_Log

table as the following :

CREATE TABLE [dbo].[Product](

      [ProductID] [int] IDENTITY(1,1) NOT NULL,

      [Name] [nvarchar](50) NULL,

      [Price] [decimal](18, 2) NULL,

      [Description] [nvarchar](max) NOT NULL,

 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

(

      [ProductID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Note : ProductID is an auto-increment identity column.

.................................................................................................

CREATE TABLE [dbo].[Product_Log](

      [LogID] [int] IDENTITY(1,1) NOT NULL,

      [ProductID] [int] NULL,

      [Name] [nvarchar](50) NULL,

      [Price] [decimal](18, 2) NULL,

      [Description] [nvarchar](max) NULL,

      [Activity] [char](1) NULL,

      [LogDate] [datetime] NULL,

 CONSTRAINT [PK_Product_Log] PRIMARY KEY CLUSTERED

(

      [LogID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[Product_Log] ADD  CONSTRAINT [DF_Product_Log_LogDate]  DEFAULT (getdate()) FOR [LogDate]

GO

Note : LogID is an auto-increment identity column. LogDate has default value set 
as getdate().

............................................................................................

Then below is the example for After Insert Trigger created under Product table :

CREATE TRIGGER [dbo].[ProductInsertTrigger]

   ON  [dbo].[Product]

   AFTER INSERT

AS

BEGIN

    declare @id int;

      declare @name varchar(50);

      declare @price decimal(10,2);

      declare @description varchar(MAX);

      declare @activity char(1);

    select @id=i.ProductID from inserted i;    

      select @name=i.Name from inserted i;     

      select @description=i.Description from inserted i;   

      select @price=i.Price from inserted i;

      select @activity='I';

      insert into Product_Log(ProductID,Name,Price,Description,Activity) values

      (@id,@name,@price,@description,@activity)

END

In the above trigger we can see that the inserted value is obtained from the logical

table, inserted.

Suppose I run the following insert query to Product table :

insert into Product(Name,Description,Price) values ('test','test desc',100)

we can see a new row is inserted also in Product_Log table as a result of

the execution of the After Insert Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate

1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360

Next is the example for After Update Trigger created under the above Product

table :

CREATE TRIGGER ProductUpdateTrigger

   ON  [dbo].[Product]

   AFTER UPDATE

AS

BEGIN

    declare @id int;

      declare @name varchar(50);

      declare @price decimal(10,2);

      declare @description varchar(MAX);

      declare @activity char(1);

    select @id=i.ProductID from inserted i;    

      select @name=i.Name from inserted i;     

      select @description=i.Description from inserted i;   

      select @price=i.Price from inserted i;

      select @activity='U';

      insert into Product_Log(ProductID,Name,Price,Description,Activity) values

      (@id,@name,@price,@description,@activity)

END

GO

For After Update trigger also the logical table, inserted is used as there is no logical

table named updated. In order to know that whether a specified column is updated, we

can use the function update(column-name). For eg. if we want to check whether the

price column is updated, we can add the following to the trigger :

if update(Price)

 some conditional query

Suppose I run the following update query to Product table :

Update Product set Name='test2',Price=200 where ProductID=1

we can see a new row is inserted also in Product_Log table as a result of

the execution of the After Update Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate

1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360

2     1     test2 200.00      test desc   U     2016-10-14 23:19:22.533

Next is the example for After Delete Trigger created under the above Product

table :

CREATE TRIGGER ProductDeleteTrigger

   ON  [dbo].[Product]

   FOR DELETE

AS

BEGIN

    declare @id int;

      declare @name varchar(50);

      declare @price decimal(10,2);

      declare @description varchar(MAX);

      declare @activity char(1);

    select @id=i.ProductID from deleted i;     

      select @name=i.Name from deleted i;

      select @description=i.Description from deleted i;    

      select @price=i.Price from deleted i;

      select @activity='D';

      insert into Product_Log(ProductID,Name,Price,Description,Activity) values

      (@id,@name,@price,@description,@activity)

END

GO

In the above trigger we can see that the deleted value is obtained from the logical

table, deleted.

Suppose I run the following delete query to Product table :

Delete from Product where ProductID=1

we can see a new row is inserted also in Product_Log table as a result of

the execution of the After Delete Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate

1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360

2     1     test2 200.00      test desc   U     2016-10-14 23:19:22.533

3     1     test2 200.00      test desc   D     2016-10-14 23:29:05.053

Instead Of Triggers :

Instead Of Triggers are triggers that gets executed before the operation that fired it.
In short this triggers are executed instead of operations like insert, update or delete.
This trigger can be created both for tables and views.There are three types of 

Instead Of Triggers :

1. Instead Of Insert Trigger

2. Instead Of Update Trigger

3. Instead Of Delete Trigger

Instead Of  Insert Triggers:

This type of trigger is executed before the insert operation that fired it and insertion
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Insert Trigger as the following :

CREATE TRIGGER [dbo].[ProductInsteadOfInsert] ON [dbo].[Product]

INSTEAD OF Insert

AS

BEGIN

DECLARE @Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)

select @Name=i.Name from inserted i

select @Price=i.Price from inserted i

select @description=i.Description from inserted i

 if(@Price<10)

 BEGIN

 RAISERROR('Cannot Insert where price < 10',16,1)

 ROLLBACK

 END

 ELSE

 BEGIN

 Insert into Product (Name,Description,Price) values (@Name,@Description,@Price)

 PRINT 'Instead Of Insert Trigger - Product inserted'

 END

END

In the trigger you can see the condition that if the price is less than 10 it will

raise the error and otherwise insertion will take place.

If I run the insert query as :

insert into Product(Name,Description,Price) values ('test','test desc',8)

It will show the error as:

Cannot Insert where price < 10

If I run the insert query as :

insert into Product(Name,Description,Price) values ('test','test desc',800)

It will insert the data to the table and print  'Instead Of Insert Trigger - Product inserted'

Instead Of  Update Triggers:

This type of trigger is executed before the update operation that fired it and updation
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Update Trigger as the
following :

CREATE TRIGGER [dbo].[ProductInsteadOfUpdate] ON [dbo].[Product]

INSTEAD OF UPDATE

AS

BEGIN

DECLARE @ID int,@Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)

select @ID=i.ProductID from inserted i

select @Name=i.Name from inserted i

select @Price=i.Price from inserted i

select @description=i.Description from inserted i

 if(@Price<10)

 BEGIN

 RAISERROR('Cannot update where price < 10',16,1)

 ROLLBACK

 END

 ELSE

 BEGIN

 Update Product set Name=@Name,Description=@Description,Price=@Price where ProductID=@ID

 PRINT 'Instead Of Update Trigger - Product updated'

 END

END

In the trigger you can see the condition that if the price is less than 10 it will

raise the error and otherwise updation will take place.

If I run the update query as :

update Product set Price=8 where ProductID=6

It will show the error as:

Cannot update where price < 10

If I run the update query as :

update Product set Price=900 where ProductID=6

It will update the corresponding data of the table and print 

'Instead Of Update Trigger - Product updated'

Instead Of  Delete Triggers:

This type of trigger is executed before the delete operation that fired it and deletion
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Delete Trigger as the
following :

CREATE TRIGGER [dbo].[ProductInsteadOfDelete] ON [dbo].[Product]

INSTEAD OF Delete

AS

BEGIN

DECLARE @ID int,@Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)

select @ID=i.ProductID from deleted i

select @Name=i.Name from deleted i

select @Price=i.Price from deleted i

select @description=i.Description from deleted i

 if(@Price>1000)

 BEGIN

 RAISERROR('Cannot delete where price>1000',16,1)

 ROLLBACK

 END

 ELSE

 BEGIN

 Delete from Product where ProductID=@ID

 PRINT 'Instead Of Delete Trigger - Product deleted'

 END

END

In the trigger you can see the condition that if the price is greater than 1000 it will

raise the error and otherwise deletion will take place. Here logical table named deleted

is used.

Suppose product with productID has price 8000 and if I run the delete query as :

Delete Product where ProductID=5

It will show the error as:

Cannot delete where price >1000

And suppose if I run the delete query for another product with price less than 1000 :

Delete Product where ProductID=6

It will delete the corresponding data of the table and print 

'Instead Of Delete Trigger - Product deleted'