CODEWITHSUNDEEP
sqloracle-databaseplsqltriggersdynamic-sql
user1663810
user1663810

Reputation: 41

Can dynamic SQL be called from a trigger in Oracle?

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 ?

Upvotes: 0

Views: 1694

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

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.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231711

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.

Upvotes: 2

Related Questions