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  |
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 #246643 is a reply to message #246619] |
Thu, 21 June 2007 09:30  |
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 #246664 is a reply to message #246619] |
Thu, 21 June 2007 10:32 |
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 #279733 is a reply to message #246683] |
Fri, 09 November 2007 17:34 |
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 #279821 is a reply to message #279775] |
Sat, 10 November 2007 16:27 |
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 #280210 is a reply to message #279834] |
Mon, 12 November 2007 16:58 |
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 #280815 is a reply to message #280587] |
Wed, 14 November 2007 20:45 |
rleishman
Messages: 3728
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 #281151 is a reply to message #280884] |
Thu, 15 November 2007 19:16 |
rleishman
Messages: 3728
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
|
|
|
|
Goto Forum:
Current Time: Wed Aug 06 05:47:34 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
