Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger how to get new and old value for nested table column?

LAVANKVMay 15 2013 — edited May 15 2013
Hi,

I have created a nested table based on the following details:

CREATE TYPE typ_item AS OBJECT --create object
(prodid NUMBER(5),
price NUMBER(7,2) )
/
CREATE TYPE typ_item_nst -- define nested table type
AS TABLE OF typ_item
/
CREATE TABLE pOrder ( -- create database table
ordid NUMBER(5),
supplier NUMBER(5),
requester NUMBER(4),
ordered DATE,
items typ_item_nst)
NESTED TABLE items STORE AS item_stor_tab
/
INSERT INTO pOrder
VALUES (800, 80, 8000, sysdate,
typ_item_nst (typ_item (88, 888)));

Now I would like to create a trigger on table pOrder for after insert or update or delete
and I would like to track the new and old value for the columns inside nested table.
Can anybody direct me how to do it?

I would like to know the sytax for it like:

declare
x number;
begin

x := :new.nestedtablecolumn;--how to get the new and old value from nested table columns
end;

Hope my question is clear.

Thanks,
Lavan
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 12 2013
Added on May 15 2013
4 comments
725 views