Triggers in Sql Server : Part - 3

In earlier articles, we learnt

• What is Trigger
• What are Magic Tables in Trigger.

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

In the example we are taking two tables, one for Sales and one for Stocks and with trigger we will update Stock table on performing insert/update/delete operations on Sales table

Lets' first create Stock and Sale Tables and populate some data in Stock Tabledata

create table stock(prod_id int, prod_name varchar(50), qty int)
go
insert into stock
select 1, 'Product-1', 10 union all
select 2, 'Product-2', 10
go
create table sale(bill_id int, prod_id int, qty int)

Now lets create 3 Triggers:

• After Insert
• After Update
• After Delete 

on Sale Table which will update the Stock table based on the operation performed.

create trigger tr_sale_insert
on sale
after insert
as
begin
    update s set qty = s.qty - i.qty
    from stock s
    inner join inserted i on s.prod_id = i.prod_id
end
go
create trigger tr_sale_delete
on sale
after delete
as
begin
    update s set qty = s.qty + d.qty
    from stock s
    inner join deleted d on s.prod_id = d.prod_id
end
go
create trigger tr_sale_update
on sale
after update
as
begin
    update s set qty = s.qty + d.qty - i.qty
    from stock s
    inner join inserted i on s.prod_id = i.prod_id
    inner join deleted d on s.prod_id = d.prod_id
end

Let's first run a query to see the data of Stock Table

select * from stock

OUTPUT

Now let's perform an insert operation on Sale Table. On insertion tr_sale_insert trigger will automatically execute and update the Stock Table.

insert into sale values(1, 1, 5)
select * from stock

OUTPUT

Look at the Stock Table after insert operation. Trigger tr_sale_insert updated the qty of prod_id 1 from 10 to 5.

Now let's perform an update operation on Sale Table. On updation tr_sale_update trigger will automatically execute and update the Stock table.

update sale set qty = 3 where bill_id = 1 and prod_id = 1
select * from stock

OUTPUT

Look at the Stock Table after update operation. Trigger tr_sale_update updated the qty of prod_id 1 from 5 to 7.

Now let's perform delete operation on Sale Table. On deletion tr_sale_delete trigger will automatically execute and update the Stock table.

delete from sale where bill_id = 1 and prod_id = 1
select * from stock

OUTPUT

Look at the Stock Table after delete operation. Trigger tr_sale_delete updated the qty of prod_id 1 from 7 to 10.

In the next part, we will take an example of Instead of Trigger

<< Prev

Home

Next >>