Forum Stats

  • 3,836,963 Users
  • 2,262,207 Discussions
  • 7,900,157 Comments

Discussions

Using DDL-Triggers to automatically create DML-Triggers for tables

herzal
herzal Member Posts: 26
edited Aug 19, 2009 7:09AM in SQL & PL/SQL
Hey guys,

I have another problem regarding triggers. My situation: I use a DDL-Trigger to create DML-Triggers for over 300 tables (because the DML-Triggers always need to be up2date and I can't rely on the other developers to update the DML-Triggers).

My problem: A table gets created. How can the DDL-Trigger find out the structure of the created table? When a column gets added to a table, how can the trigger know the new column? The only way I see currently is parsing the triggering SQL-statement, and that s****.

Thanks for your help =)
Tagged:

Answers

  • Herzal,

    Your idea to use a trigger to create DML triggers seems dangerous. It looks your organization has no change procedures in place, otherwise you wouldn't need to automate this.
    Tables don't change every day, and they don't change on the fly. Changes are controlled in releases.
    In my opinion the developer could be and should be bothered to call a trigger generator.

    Assuming ordinary DML-triggers, the trigger consists of
    - a series of assignments, assigning :new.<columname> to a field in a record. The record is based on a SUBTYPE and the SUBTYPE is <table>%ROWTYPE
    obviously the series of assignments the generator needs can be retrieved from user_tab_columns. There is no need to parse anything, or you must disclose much more details.
    - the record is passed to a procedure as parameter, the procedure performs the actual insert. The INSERT statement uses record syntax, so is almost generic.

    Doing so, one doesn't alter the trigger, one simply recreates it with the new columnlist, using the generator.

    Hth

    --------------
    Sybrand Bakker
    Senior Oracle DBA
  • herzal
    herzal Member Posts: 26
    edited Aug 19, 2009 5:43AM
    Thanks for your reply.

    I totally agree with you that our developers should be bothered to call a trigger generator. But someone WILL forget it. So I thought I use DDL-Triggers and call the trigger generator.

    As for parsing the statement: the problem is that if I use a DDL-Trigger, I know when a table gets created / altered, but since the changes are not final when the trigger is called, there are no entries in user_tab_columns for the new columns.

    If there is no other way than parsing the triggering SQL-statement to find out which columns get added, I will talk to my boss and tell him that the only possible way to do this s**** and that we simply need to force all developers to call the trigger generator. But I would be glad to find a way that uses a totally automatic approach.

    As always, thanks for your help =)

    Edited by: Herzal on 19.08.2009 11:43
  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    edited Aug 19, 2009 6:55AM
    Hi Herzal,

    Maybe you can get some inspiration from [DDL in Triggers|http://www.oracle.com/technology/oramag/oracle/08-jul/o48asktom.html]
    - key point is to delay the query for user_tab_columns by use of DBMS_JOB

    Wonder why you need all those triggers, maybe something else can take their place.

    Edit:
    A more pragmatic and less dangerous solution would be just to create a report finding those missing/out of sync triggers, and schedule that to run on a regular basis.


    Regards
    Peter

    Edited by: Peter on Aug 19, 2009 3:54 AM
    - Commented
    Peter Gjelstrup
  • herzal
    herzal Member Posts: 26
    Thank you, Peter, I think, DBMS_JOB will work for my case =)
This discussion has been closed.