3 Replies Latest reply: Jul 4, 2012 2:23 AM by 934999 RSS

    Triggers in Workspace Manager

    921148
      I've got some problems with the workspace manager and triggers in 10g:

      If you have a trigger defined on a table and you version this table, ORACLE will create 3 "instead of"-triggers on the view representing the table.
      The "content" of the trigger will be created in a wm$ procedure.

      My question is: How can I change my trigger without unversioning the table?

      As an example, consider the following two tables:

      -- Create Data Table
      CREATE TABLE MY_TABLE
      (
           X_ID NUMBER(28) NOT NULL,
           V_TEXT VARCHAR2(200),
           V_COMMENT VARCHAR2(4000)
      );

      -- The Table's PK
      ALTER TABLE MY_TABLE ADD CONSTRAINT PK_MY_TABLE
           PRIMARY KEY (X_ID)
      USING INDEX;

      -- Create Registry Table
      CREATE TABLE MY_REGISTRY_TABLE
      (
           X_ID NUMBER(28) NOT NULL,
           V_OLD_TEXT VARCHAR2(200),
           V_OLD_COMMENT VARCHAR2(4000),
           V_NEW_TEXT VARCHAR2(200),
           V_NEW_COMMENT VARCHAR2(4000),
           D_WHEN DATE NOT NULL
      );

      Now, we create a trigger that automatically registers the changes made on MY_TABLE:

      -- Create Trigger
      CREATE OR REPLACE TRIGGER TR_MY_TABLE
      AFTER INSERT OR UPDATE OR DELETE ON MY_TABLE
      FOR EACH ROW
      BEGIN
           
           INSERT INTO MY_REGISTRY_TABLE(
                X_ID
                ,V_OLD_TEXT
                ,V_OLD_COMMENT
                ,V_NEW_TEXT
                ,V_NEW_COMMENT
                ,D_WHEN
                )
           VALUES (
                NVL(:old.X_ID, :new.X_ID)
                ,:old.V_TEXT
                ,:old.V_COMMENT
                ,:new.V_TEXT
                ,:new.V_COMMENT
                ,sysdate
                );
           
      END TR_MY_TABLE;
      /

      Now, we decide to version MY_TABLE.

      exec dbms_wm.enableversioning('MY_TABLE');

      This turns MY_TABLE into 2 data tables and a collection of views.
      The trigger has changed as well. The original trigger has disappeared, and there are 3 "INSTEAD OF"-triggers on the view MY_TABLE:
      OVM_Delete_102
      OVM_Insert_102
      OVM_Update_102

      These three triggers in the end call a system generated procedure:
      wm$proc_udt_187

      If I try to change this procedure, the system won't let me do that.

      So, my question is, how can I change my trigger on MY_TABLE without unversioning the table?