[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

triggers oracle 10g old / new values

i am trying to see if in oracle their is a quick way to get the entire row for old and new values.

I want the trigger to call a procedure, and i want to pass two variable to the procedure

1 would be the the old_row_values, the other would be the new row_values.

Creating the procedre with teh record data type is no problem,  and I could assign each column of a record data type  in the trigger.

But i wanted to know if there was something like

OLD_RECORD := :OLD row
NEW_RECORD := :NEW row

?? if not please elt me knwo and at least that will stop me going  down a bad path
0
jhacharya
Asked:
jhacharya
5 Solutions
 
sdstuberCommented:
No,  the :old and :new virtual records can not be referenced like that.

you can only use them as pointers to their individual fields

:old.some_column

:new.some_column
0
 
slightwv (䄆 Netminder) Commented:
0
 
CarlsbergFTWCommented:
As a workaround you might try concatenating the values of the targeted columns, tho it's just am idea (i'm not sure it works but you can try), else , like the other experts said , it's not possible.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
schwertnerCommented:
You can old and new values of a record only in UPDATE (before, after) trigger to the table.
In INSERT or DELETE triggers there will not be both 'new' and 'old' values.

So such a trigger will be your working environment.

You can access old and new COLUMN values either hardcoded or dynamic.

For dinamic access use the view USER_TAB_COLUMNS selecting only the particular table and you will get the list of the column names.

PL/SQL is able to create strings like ':old.column_name'.

after taht you can do what you need, following the recommendation of the other experts - concatenating the values or store them in historical table. The last option is the most used way.
0
 
sdstuberCommented:
if you want to use records,  then rather than concatenating  (which will require string parsing to pull it apart)

define a record type for your table in some package (preferably the same package where you define your procedure)

then simply construct instances of the record type from the pseudo-records

DECLARE
    v_old   your_package.your_record_type;
    v_new   your_package.your_record_type;
BEGIN
    v_old.field1  := :old.field1;
    v_old.field2  := :old.field2;
    v_old.field3  := :old.field3;
    v_old.field4  := :old.field4;
    v_old.field5  := :old.field5;

    v_new.field1  := :new.field1;
    v_new.field2  := :new.field2;
    v_new.field3  := :new.field3;
    v_new.field4  := :new.field4;
    v_new.field5  := :new.field5;

    your_package.your_procedure(v_old, v_new);
END;
0
 
jhacharyaAuthor Commented:
thanks for the help at least is stopped me going down a path that is not possible ...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now