In earlier articles, we learnt
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,
Script to create product and product_log Table
Now lets create 3 triggers -
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.
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.
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.
Look after Delete Operation, trigger tr_product_delete inserted log entries in product_log but actual delete operation on product table is not performed.
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
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
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_logOUTPUT
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_logOUTPUT
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_logOUTPUT
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 |