My Oracle Support Banner

ORA-04088: ERROR DURING EXECUTION OF TRIGGER 'WMSYS.WM$1$UPDATE$' when Using 'DEFFERABLE NO VALIDATE' Unique Constraint Against a Version Enable Table (Doc ID 2041458.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Workspace Manager - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Getting issues using DEFFERABLE NO VALIDATE unique CONSTRAINT against Version enabled tables. Following testcase demonstrates the issue -

SQL> CREATE TABLE i25878_t (
 2    id NUMBER PRIMARY KEY
 3    ,action_folder_id NUMBER NOT NULL
 4    ,site_name VARCHAR2(100) NOT NULL
 5    ,waters_type_id NUMBER
 6  );

Table created.

SQL> EXEC DBMS_WM.ENABLEVERSIONING('I25878_T','VIEW_WO_OVERWRITE');

PL/SQL procedure successfully completed.

SQL> INSERT INTO i25878_t (id,action_folder_id,site_name,waters_type_id) VALUES (1,1,'A',NULL);

1 row created.

SQL> INSERT INTO i25878_t (id,action_folder_id,site_name,waters_type_id) VALUES (2,1,'A',NULL);

1 row created.

SQL> INSERT INTO i25878_t (id,action_folder_id,site_name,waters_type_id) VALUES (3,1,'B',NULL);

1 row created.

SQL> INSERT INTO i25878_t (id,action_folder_id,site_name,waters_type_id) VALUES (4,2,'A',NULL);

1 row created.

SQL> EXEC DBMS_WM.BEGINDDL('I25878_T');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE i25878_t_lts ADD CONSTRAINT u_i25878_t UNIQUE (action_folder_id,site_name) DEFERRABLE ENABLE NOVALIDATE;

Table altered.

SQL> EXEC DBMS_WM.COMMITDDL('I25878_T');

PL/SQL procedure successfully completed.

SQL> -- attempt to set (id=>1 UK values)=(id=>[1,2] UK values)
SQL> UPDATE i25878_t
 2  SET action_folder_id=action_folder_id
 3    ,site_name=site_name
 4    ,waters_type_id=waters_type_id
 5  WHERE id=1;
UPDATE i25878_t
      *
ERROR at line 1:
ORA-20232: unique constraint (U4RRCKRP.U_I25878_T) violated
ORA-06512: at "U4RRCKRP.OVM_UPDATE_130", line 3
ORA-04088: error during execution of trigger 'U4RRCKRP.OVM_UPDATE_130'

SQL> ROLLBACK;

Rollback complete.

SQL> -- attempt to set (id=>1 UK values)=(id=>[1,2] UK values)
SQL> UPDATE i25878_t
 2  SET action_folder_id=action_folder_id
 3    ,site_name=site_name
 4    ,waters_type_id=1
 5  WHERE id=1;
UPDATE i25878_t
      *
ERROR at line 1:
ORA-20232: unique constraint (U4RRCKRP.U_I25878_T) violated
ORA-06512: at "U4RRCKRP.OVM_UPDATE_130", line 3
ORA-04088: error during execution of trigger 'U4RRCKRP.OVM_UPDATE_130'

SQL> ROLLBACK;

Rollback complete.

SQL> -- attempt to set (id=>1 UK values)=(new UK values)
SQL> UPDATE i25878_t
 2  SET action_folder_id=action_folder_id
 3    ,site_name='C'
 4    ,waters_type_id=waters_type_id
 5  WHERE id=1;
.
1 row updated.

SQL> ROLLBACK;

Rollback complete.

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.