Home » SQL & PL/SQL » SQL & PL/SQL » Trigger - can I access :NEW AND :OLD dynamically???
Trigger - can I access :NEW AND :OLD dynamically??? [message #246619] Thu, 21 June 2007 08:47 Go to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Is there a way to access the :NEW and :OLD values dynamically within a Trigger, I am trying to get all the columns from the user_tab_columns for the table and loop through and call a procedure passing in the :NEW and :OLD values, but when I try to compile Oracle complains that of "Bad Bind Variable"...

Is there any other way to achieve this????
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #246632 is a reply to message #246619] Thu, 21 June 2007 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you can't. :NEW and :OLD are fixed record types.
What is the difficulty to write each record field?

Regards
Michel
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #246634 is a reply to message #246632] Thu, 21 June 2007 09:15 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel, thats a valid question....

In future If any new columns added, then I have to include that stub within the trigger manually, If I can loop through the user_tab_columns dynamically, then I can avoid that????

does it make any sense????
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #246637 is a reply to message #246634] Thu, 21 June 2007 09:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you add a new column either you have to do something with it and you modify the code to do so, either you have nothing to do with the values of this new column and there is nothing to change.
I see no need to dynamically and automatically add code in triggers.

Regards
Michel
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #246643 is a reply to message #246619] Thu, 21 June 2007 09:30 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
For the most part I agree with you Michel.

However, if you are writing to history or audit tables, there
may be a case for a dynamic change.

This being said, I still would think that you would want to review the validity and business rules of tracking the new column. This should not be a common occurence in a production environment, so the overhead is minimal to manually change the trigger.

Marc


Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #246646 is a reply to message #246637] Thu, 21 June 2007 09:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I had something in that direction once, where they wanted to audit user-selectable columns with their before/after values online into a specified XML structure.

The table itself didn't change, but the columns they wanted to log changed every five minutes. Wink

Since I didn't find any way to do it dynamically inside the trigger either I wound up creating the trigger itself with a dynamic script that re-created the trigger every time they wanted the logging to change.
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #246664 is a reply to message #246619] Thu, 21 June 2007 10:32 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel, Marc, Thomas:-

Thank you very much for the replies, as per Michel I have to stick to do it manually, instead dynamic, I have created a script to produce the create trigger script, which is handy, hence when next time if there are any column additions then all I have to do is just run this script and produce the script to create trigger....

Once again Thanks a lot guys...

Cheers,
Saran.
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #246683 is a reply to message #246664] Thu, 21 June 2007 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the correct way, yes.

Regards
Michel
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #279733 is a reply to message #246683] Fri, 09 November 2007 17:34 Go to previous messageGo to next message
jmrwired
Messages: 3
Registered: November 2007
Junior Member
Michel, I saw your reply to a forum question involving the following....

Is there a way to access the :NEW and :OLD values dynamically within a Trigger.....

No, you can't. :NEW and :OLD are fixed record types.


I've come close...

insert into audit1 (newvalue) values(:new.sacctnum);

The above statement works fine within the trigger. But if executed as a string using Execute Immediate, I get...

ORA-01008: not all variables bound.

What do you think? Is this at all possible?
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #279775 is a reply to message #279733] Sat, 10 November 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't use execute immediate in the correct way.
Post your code if you want a more detailed answer.

Regards
Michel
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #279821 is a reply to message #279775] Sat, 10 November 2007 16:27 Go to previous messageGo to next message
jmrwired
Messages: 3
Registered: November 2007
Junior Member
To keep things simple, the main statement is below. Basically, it builds an insert statement, which I can shorten for testing into...

-----
v_sql := 'insert into audit1 (newvalue) values (:new.address1)';

BEGIN
EXECUTE IMMEDIATE (v_sql);
END;
-----

The above creates the error: ORA-01008: not all variables bound

However, if I abandon the dynamic aspect and just execute the same insert statement directly from the trigger, it works fine.
i.e.
--insert into audit1 (newvalue) values(:new.sacctnum); --this works fine

Below is more of the actual dynamic part. The trigger steps through each field in the table (not shown). So with very little adjusting the trigger can easily be setup once on any table and get all fields. The same basic trigger works fine in MSSQL. I'm a novice, but it seems that this trigger is just so close to working. Thanks in advance for revisiting this topic.




v_sql := 'insert INTO Audit1 (Type, TableName, PK, FieldName, OldValue, NewValue,UpdateDate, UserName, Origin, Target) (';

v_sql := v_sql || ' select ''' || v_Type || '''';

v_sql := v_sql || ',''' || v_TableName || '''';

v_sql := v_sql || ',' || v_PKSelect;

v_sql := v_sql || ',''' || v_fieldname || '''';

v_sql := v_sql || ',substr(to_char(:OLD.' || v_fieldname || '),1,1000)';

v_sql := v_sql || ',substr(to_char(:NEW.' || v_fieldname || '),1,1000)';

v_sql := v_sql || ',''' || to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS') || '''';

v_sql := v_sql || ',''' || v_UserName || '''';

v_sql := v_sql || ',''' || v_Origin || '''';

v_sql := v_sql || ',''' || v_Target || '''';

v_sql := v_sql || ' from dual';

v_sql := v_sql || ' where :NEW.' || v_fieldname || ' <> :OLD.' || v_fieldname;

v_sql := v_sql || ' or (:NEW.' || v_fieldname || ' is null and length(:OLD.' || v_fieldname || ') >0 and :OLD.' || v_fieldname || ' is not null)';

v_sql := v_sql || ' or (:NEW.' || v_fieldname || ' is not null and length(:NEW.' || v_fieldname || ') >0 and :OLD.' || v_fieldname || ' is null))';

Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #279823 is a reply to message #279821] Sat, 10 November 2007 16:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #279834 is a reply to message #279821] Sun, 11 November 2007 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know how to use, get input variables and return values from execute immediate, see:
PL/SQL User's Guide and Reference
Chapter 13 PL/SQL Language Elements
Section EXECUTE IMMEDIATE Statement

Regards
Michel

Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #280210 is a reply to message #279834] Mon, 12 November 2007 16:58 Go to previous messageGo to next message
jmrwired
Messages: 3
Registered: November 2007
Junior Member
Thanks, Barbara. Your link to the AskTom topic is quite definitive.

I didn't do it exactly like Tom suggested. I kept it all within a single trigger. And instead of dynamic sql, I created a script to generate all of the insert statements for each field in a given table. (that part actually is very similar to Tom's code). But I just paste the insert statements into the trigger. It's easy enough to regenerate should the table's columns ever change.

Below is a sample of my insert generation. Michel, you should chime in if you know of a definitive way to do the same via dynamic sql. If you do, AskTom would be quite interested to know, as would I.

select 'insert INTO Audit1 (Type, TableName, PK, FieldName, OldValue, NewValue,UpdateDate, UserName, Origin, Target) ('
|| ' select v_Type , v_TableName, v_PK'
|| ' , ''' || column_name || ''''
|| ', substr(to_char(:OLD.' || column_name || '),1,1000)'
|| ', substr(to_char(:NEW.' || column_name || '),1,1000)'
|| ', sysdate, v_UserName, v_Origin, v_Target'
|| ' from dual'
|| ' where :NEW.' || column_name || ' <> :OLD.' || column_name
|| ' or (:NEW.' || column_name || ' is null and length(:OLD.' || column_name || ') >0 and :OLD.' || column_name || ' is not null)'
|| ' or (:NEW.' || column_name || ' is not null and length(:NEW.' || column_name || ') >0 and :OLD.' || column_name || ' is null));'
from user_tab_columns where table_name = upper('VENDOR')
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #280498 is a reply to message #246619] Tue, 13 November 2007 21:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
oracle has its own auditing right? Maybe you should check it out before you write a lot of your own code. It may not be what you want, but you should at least look.

Try googling for Oracle10g Asynchronous Change Data Capture.

good luck, Kevin
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #280541 is a reply to message #280498] Wed, 14 November 2007 01:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Back on the topic of rebuilding a trigger after a database change, you could possibly use an AFTER ALTER DDL trigger to rebuild the triggers for you.

Ross Leishman
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #280587 is a reply to message #246619] Wed, 14 November 2007 02:50 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I did some experimenting on this issue a while back.
Based upon ideas from other developers I came up with following solution.

I won't say it's THE SOLUTION, but it suites our requirements perfectly.
To be honest: it's for the moment only used for a table or three.

I've attached a script which contains a package and a sample-trigger.

Feel free to read/comment/applaud Smile
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #280815 is a reply to message #280587] Wed, 14 November 2007 20:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Reasonably ingenious. I was aware that you cannot use :old and :new in dynamic pl/sql, but I did not realise you could use them in Dynamic SQL.

You learn something new every day.

Since the text of the SQL is fixed for every table, I would store it away in a persistent package associative array keyed by the table name. Then you only need to execute the USER_TAB_COLUMNS SQL once per session rather than once per row.

I'm still a little concerned about context switches to perform the SELECT FROM DUAL, but it's not without merit.

Ross Leishman
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #280884 is a reply to message #280815] Thu, 15 November 2007 01:45 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
rleishman wrote on Thu, 15 November 2007 03:45

You learn something new every day.


Couldn't agree more, because ...

rleishman wrote on Thu, 15 November 2007 03:45

Since the text of the SQL is fixed for every table, I would store it away in a persistent package associative array keyed by the table name. Then you only need to execute the USER_TAB_COLUMNS SQL once per session rather than once per row.


I didn't even think about THAT! Embarassed

rleishman wrote on Thu, 15 November 2007 03:45

I'm still a little concerned about context switches to perform the SELECT FROM DUAL, but it's not without merit.

Ross Leishman


Well, not having real performance issue at the moment. But we'll keep having a keen eye
Re: Trigger - can I access :NEW AND :OLD dynamically??? [message #281151 is a reply to message #280884] Thu, 15 November 2007 19:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In a DW or batch system, this would perform tragically.

In an OLTP system, you wouldn't notice a difference in response time. What you WOULD notice though is an increased load on the server, and a corresponding decrease in capacity (as measured by max # concurrent users). This sort of thing is horribly difficult to benchmark though.

Ross Leishman
Previous Topic: One result missing in a product
Next Topic: Partition Exchange in Procedure
Goto Forum:
  


Current Time: Fri Dec 09 03:41:36 CST 2016

Total time taken to generate the page: 0.17044 seconds