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
LVL 2
Jayesh AcharyaTechnichal ConsultantAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
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
 
CarlsbergFTWConnect With a Mentor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
schwertnerConnect With a Mentor Commented:
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
 
sdstuberConnect With a Mentor Commented:
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
 
Jayesh AcharyaTechnichal ConsultantAuthor Commented:
thanks for the help at least is stopped me going down a path that is not possible ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.