This discussion is archived
3 Replies Latest reply: Jul 4, 2012 12:23 AM by 934999 RSS

Triggers in Workspace Manager

921148 Newbie
Currently Being Moderated
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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points