Lets talk about triggers as I mentioned in my last post that I will come online to write about this feature of PL/SQL is to make you understand that how much easily you can control things in your database...
Okay now lets talk about below trigger that what really its doing...
If you remember about my previous posting and especially about Stock Tracking System... The question is that how it will automatically track and keep record of stock after each and every sale, purchase and wastage if any. Also sale return and purchase return puts effect on stock...
Lets see below trigger and talk about it...
create or replace trigger price_history
before update of cost_price
on stock
for each row
begin
insert into price_history
values
(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
end;
/
Okay now lets talk about below trigger that what really its doing...
If you remember about my previous posting and especially about Stock Tracking System... The question is that how it will automatically track and keep record of stock after each and every sale, purchase and wastage if any. Also sale return and purchase return puts effect on stock...
Lets see below trigger and talk about it...
create or replace trigger price_history
before update of cost_price
on stock
for each row
begin
insert into price_history
values
(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
end;
/
Don't be confused about this trigger as I was when saw this coding for the first time...
create or replace trigger price_history
it will create trigger and will overwrite the trigger name 'price_history', if one already there...
before update of cost_price
Here we are asking oracle to fire the trigger before changes on cost_price are saved...
on stock
The table name in which changes are being made...
for each row
The trigger will fire for each row, whether you change one record or many...
begin
after this part the trigger body will start...
insert into price_history
It will insert record in table name price_history which is already created in the database...
values
part of command...
(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
if you note I am using record type as old because I want to record the history of cost_price of items...
end;
part of command...
(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
if you note I am using record type as old because I want to record the history of cost_price of items...
end;
Will close the trigger...
Whenever the cost_price of existing item is updated or changed, the trigger will fire and it will add old details of the items with cost_price to table named price_history...
Thank you for your time and next time we will meet for something complex to calculate data...
No comments:
Post a Comment