A customer writes ....

"We are in the process of upgrading from V10 to 11.5 of IDS and as part
of upgrade I was looking at using the new Trigger logic to allow the old
and new values to be referenced in a called stored procedure. However,
on using this I had severe performance issues.

For example, the current insert trigger on a large table (109 columns)
tests some conditions and if passed inserts a copy of the record
(including selecting some additional data using direct sql and stored
procedure calls) into an audit table. Then 3 extra procedures are
called to carry out bits of work.

In test I created 200 records in the table and it took at a maximum 2
seconds to run over a few tests.

I altered the trigger to only call one stored procedure which would
reference the new and old values using the syntax:

CREATE TRIGGER trg_in_pres_slot INSERT ON pres_slot

FOR EACH ROW

( EXECUTE PROCEDURE usp_trigger_pres_slot() WITH TRIGGER REFERENCES );

I put the logic I had from the old trigger into the stored procedure and
it worked fine except, it took a lot longer. For the 200 records it
repeatedly took between 14-16 seconds to complete. I started taking
bits out to see what was the issue until all I had was an empty stored
procedure and it was still taking 6 seconds to add 200 records with no
other actions going on, so longer than the previous trigger statement
which had things going on.

Needless to say, I have abandoned this approach, which is a pity as it
keeps the logic in one place and I can just alter the procedure rather
than having to drop and recreate the trigger if I make a change.

Can anybody explain this apparent performance issue with this new
syntax?"