Triggers in Sql Server : Part - 4

In earlier articles, we learnt

• What is trigger
• What are magic tables in trigger
• After Trigger Example

In this article, we will take an example of Instead of trigger and will see how it works.

In the example we are taking two tables,

• Product for maintaining product master details
• Product Log for logging on product master

With trigger we will create log in product_log table on performing insert/update/delete operations on product table

Script to create product and product_log Table

create table product (prod_id int, prod_desc varchar(10))
go
create table product_log (prod_id int, prod_desc varchar(10), action char(1))
go
insert into product values(1, 'prod-1')

Now lets create 3 triggers -

• Instead of Insert
• Instead of Update
• Instead of Delete

on product Table which will create log in product_log table based on the operation performed.

create trigger tr_product_insert
on product
instead of insert
as
begin
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'I' from inserted
end
go
create trigger tr_product_update
on product
instead of update
as
begin
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'B' from inserted
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'A' from deleted
end
go
create trigger tr_product
on product
instead of delete
as
begin
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'D' from deleted
end

Let's perform an Insert operation on product table. On insertion tr_product_insert trigger will automatically execute and create log entries in product_log table.

insert into product values(2, 'prod-2')
select * from product
select * from product_log

OUTPUT

Look after Insert Operation, Trigger tr_product_insert inserted a log entry in product_log but actual Insert Operation on product table is not performed.

Now let's perform an Update Operation on product table. On updation tr_product_update trigger will automatically execute and create log entries in product_log table.

update product set prod_desc = 'prod-2' where prod_id = 1
select * from product
select * from product_log

OUTPUT

Look after Update Operation, Trigger tr_product_update inserted log entries in product_log but actual update operation on product table is not performed.

Now let's perform a Delete Operation on product table. On deletion tr_product_delete trigger will automatically execute and create log entries in product_log table.

delete from product where prod_id = 1
select * from product
select * from product_log

OUTPUT

Look after Delete Operation, trigger tr_product_delete inserted log entries in product_log but actual delete operation on product table is not performed.

<< Prev

Home