?
Solved

triggers oracle 10g old / new values

Posted on 2012-03-20
6
Medium Priority
?
818 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 800 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 77

Assisted Solution

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

Assisted Solution

by:CarlsbergFTW
CarlsbergFTW earned 400 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 400 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 800 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 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