Solved

triggers oracle 10g old / new values

Posted on 2012-03-20
6
807 Views
Last Modified: 2012-03-20
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
Comment
Question by:jhacharya
6 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 200 total points
ID: 37742780
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 37742788
0
 
LVL 3

Assisted Solution

by:CarlsbergFTW
CarlsbergFTW earned 100 total points
ID: 37742933
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 37743954
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 37743989
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
 
LVL 2

Author Closing Comment

by:jhacharya
ID: 37744273
thanks for the help at least is stopped me going down a path that is not possible ...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now