Home » SQL & PL/SQL » SQL & PL/SQL » trigger : problem in old.columnvalue(urgent)
trigger : problem in old.columnvalue(urgent) [message #191368] Wed, 06 September 2006 00:48 Go to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
Hi friends,

I am using following code to update the stock:
just check this , anything i have to modify...?

SQL> create or replace trigger stock_update
2 before insert or update or delete of item_code,qty on purchase_items
3 for each row
4 begin
5 if (:old.item_code = :new.item_code) then
6 if inserting then
7 update stock set qty=qty + :new.qty where stock.item_code = :new.item_code;
8 end if;
9 else
10 insert into stock values (:old.item_code,:new.qty);
11 end if;
12 end;
13 /

when i run this trigger the old.item_code always shows null

The concept is if Item_code was already there then it will update
the table otherwise it should insert

what is wrong in that code, Any one kinldy help me

thanx and regards
Selva.R
Re: trigger : problem in old.columnvalue(urgent) [message #191371 is a reply to message #191368] Wed, 06 September 2006 01:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
An INSERT does not have an :old value. :old values only apply to updates and deletes. :new values only apply to inserts and updates. Deletes don't have :new values.

Basically you have to identify the operation first:
IF INSERTING THEN
-- perform insert operation
ELSIF UPDATING THEN
-- perform update operation
ELSE -- DELETING
-- PERFORM delete operation
END IF;


MHE
Re: trigger : problem in old.columnvalue(urgent) [message #191374 is a reply to message #191371] Wed, 06 September 2006 01:31 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
thanx maheer

Re: trigger : problem in old.columnvalue(urgent) [message #191376 is a reply to message #191374] Wed, 06 September 2006 01:33 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
No problem. I hope you see the logic: a delete can't have a :new value because there is no new value and an insert does not have an :old value because there was none.

MHE
Re: trigger : problem in old.columnvalue(urgent) [message #191381 is a reply to message #191376] Wed, 06 September 2006 01:47 Go to previous messageGo to next message
jai_o7
Messages: 3
Registered: August 2006
Location: Mumbai
Junior Member
Hi,

Ur Problem will sort to major extend with the above solutions, but there are 2 mistakes that still exists in the code:

1) if (:old.item_code = :new.item_code) then
Instead
Go for
if (nvl(:old.item_code,0) = :new.item_code) then

2) insert into stock values (:old.item_code,:new.qty);
Instead
Go for
insert into stock values (nvl(:old.item_code,0),:new.qty);

Regards,
Jai
Re: trigger : problem in old.columnvalue(urgent) [message #191394 is a reply to message #191381] Wed, 06 September 2006 02:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
jai_o7,
are you sure you know the business rules of the OP? Does he want to insert a record with item_code 0? I wouldn't bet on it...

MHE
Re: trigger : problem in old.columnvalue(urgent) [message #191398 is a reply to message #191394] Wed, 06 September 2006 02:50 Go to previous message
jai_o7
Messages: 3
Registered: August 2006
Location: Mumbai
Junior Member
Hi Maaher,

'0' in the given context specifies any suitable default value. Anyways if there is a NULL in the Expression then it will always go in the Else condition, even if both are NULL.

Regards,
Jai
Previous Topic: demobld
Next Topic: Use of DEFAULT in parameter list for spec and body
Goto Forum:
  


Current Time: Thu Dec 08 18:44:56 CST 2016

Total time taken to generate the page: 0.05107 seconds