Solved

triggers oracle 10g old / new values

Posted on 2012-03-20
6
809 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 74

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 48

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 74

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Repeat query 13 61
error in my cursor 5 41
update statement in oracle 9 29
Procedure syntax 5 38
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

861 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