Can dynamic SQL be called from a trigger in Oracle?

Question

I have a dozen tables of whom I want to keep the history of the changes. For every one I created a second table with the ending _HISTO and added fields modtime, action, user.

At the moment before I insert, modify or delete a record in this tables I call ( from my delphi app ) a oracle procedure that copies the actual values to the histo table and then do the operation.

My procedure generates a dynamic sql via DBA_TAB_COLUMNS and then executes the generated ( insert into tablename_histo ( fields s ) select fields, sysdate, 'acition', userid from table_name

I was told that I can not call this procedure from a trigger because it has to select the table the trigger is triggered on. Is this true ? Is it possible to implement what I need ?

Why does it need to be dynamic? Can't you update each trigger to reflect any new columns at the same time that you modify the base and history tables it refers to? — Alex Poole
– Alex Poole, Commented Sep 25, 2012 at 18:25
Also, Have you looked at oracle fine grained auditing and/or dbms workspace packages? What is your database version ? — Rajesh Chamarthi
– Rajesh Chamarthi, Commented Sep 25, 2012 at 18:30

Justin Cave · Accepted Answer · 2012-09-25 18:46:52Z

Assuming you want to maintain history using triggers (rather than any of the other methods of tracking history data in Oracle-- Workspace Manager, Total Recall, Streams, Fine_Grained Auditing etc.), you can use dynamic SQL in the trigger. But the dynamic SQL is subject to the same rules that static SQL is subject to. And even static SQL in a row-level trigger cannot in general query the table that the trigger is defined on without generating a mutating table exception.

Rather than calling dynamic SQL from your trigger, however, you can potentially write some dynamic SQL that generates the trigger in the first place using the same data dictionary tables. The triggers themselves would statically refer to :new.column_name and :old.column_name. Of course, you would have to either edit the trigger or re-run the procedure that dynamically creates the trigger when a new column gets added. Since you, presumably, need to add the column to both the main table and the history table, however, this generally isn't too big of a deal.

Bob Jarvis - Слава Україні · Accepted Answer · 2012-09-26 11:22:10Z

Oracle does not allow a trigger to execute a SELECT against the table on which the trigger is defined. If you try it you'll get the dreaded "mutating table" error (ORA-04091), and while there are ways to get around that error they add a lot of complexity for little value. If you really want to build a dynamic query every time your table is updated (IMO this is a bad idea from the standpoint of performance - I find that metadata queries are often slow, but YMMV) it should end up looking something like

strAction := CASE
               WHEN INSERTING THEN 'INSERT'
               WHEN UPDATING THEN 'UPDATE'
               WHEN DELETING THEN 'DELETE'
             END;

INSERT INTO TABLENAME_HISTO
  (ACTIVITY_DATE, ACTION, MTC_USER,
   old_field1, new_field1, old_field2, new_field2)
VALUES
  (SYSDATE, strAction, USERID,
   :OLD.field1, :NEW.field1, :OLD.field2, :NEW.field2)

Share and enjoy.

Collectives™ on Stack Overflow

Can dynamic SQL be called from a trigger in Oracle?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related