>> We have lots of row level audit triggers that audit update and delete on
>> every column for each row.
>> But bind variable can not be used in the trigger.
David
First point: PL/SQL does the business with bind variables (as long as you use PL/SQL variables); you don't have to do any nasty dynamic SQL (native or DBMS_SQL) yourself.
>>The alternative is to create a procedure that uses bind variable and is called by the trigger.
>>Does any one have experience on this? How is the performance with calling the
>>procedure from trigger back and forth? The database is shared by many
>>home grow and vendor applications.
I'm assuming your code is auditing column by column (rather than does something like:
IF :new.my_first_column != :old.my_first_column THEN
insert into audit_trail(column, update_date, pk_column, old_value, new_value)
values ('MY_FIRST_COLUMN', sysdate, :new.pk_column, :old.my_first_column, :new.my_first_column);
END IF;
IF :new.my_first_column != :old.my_first_column THEN
insert into audit_trail(column, update_date, pk_column, old_value, new_value)
values ('MY_SECOND_COLUMN', sysdate, :new.pk_column, :old.my_second_column, :new.my_second_column);
END IF;
and so on for all columns (and the calls are more complex in real life, of course).
You can either:
1) copy the column name into a variable in each block:
IF :new.my_first_column != :old.my_first_column THEN
l_column := 'MY_FIRST_COLUMN'
insert into audit_trail(column, update_date, pk_column, old_value, new_value)
values (l_column, sysdate, :new.pk_column, :old.my_first_column, :new.my_first_column);
END IF;
IF :new.my_first_column != :old.my_first_column THEN
insert into audit_trail(column, update_date, pk_column, old_value, new_value)
values (l_column, sysdate, :new.pk_column, :old.my_second_column, :new.my_second_column);
END IF;
The insert statements should (from V$SQL) look identical.
2) create a packaged procedure to handle column audit
IF :new.my_first_column != :old.my_first_column THEN
pk_audit.insert('MY_FIRST_COLUMN', sysdate, :new.pk_column, :old.my_first_column, :new.my_first_column);
END IF;
IF :new.my_first_column != :old.my_first_column THEN
pk_audit.insert('MY_SECOND_COLUMN', sysdate, :new.pk_column, :old.my_second_column, :new.my_second_column);
END IF;
The insert will now use the very same cursor in each case, minimising the load on the shared pool.
I recommend (2), and this approach is very widely used.
In the old days (Oracle 7) the slowness of trigger compilation (at run time) meant that factoring out as much code from triggers into packages was strongly encouraged for performance reasons. That's less of an issue now (I believe) but I still think it is good practice to reduce the size of triggers. Remember that the cost of a PL/SQL package call is orders of magnitude less than the cost of an extra SQL parse - even a soft one - or the cost of managing a larger cursor cache.
>> If we change the cursor_sharing to force, what are the pros and cons?
I definitely don't recommend setting up CURSOR_SHARING=FORCE or SIMILAR except as a temporary workaround to 3rd party vendor's non-binding code. Given that you say you have have many applications, messing with this could have unexpected side effects (eg similar SQL that currently intentionally uses different execution plans may be forced to use the same one). If it isn't broke, don't break it youself! If you ever DO need to use CURSOR_SHARING, you should limit the scope to a single application if possible (eg by setting the parameter for the session in a LOGON trigger, rather than setting it instance wide).
HTH
Regards Nigel
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 06 2006 - 11:49:55 CST
