Skip to main content

Search

Welcome to Tibero GTS!

NOT NULL Error TBR-10005 Occurs When Performing Merge Into SQL on Table with Trigger

Document Type | Troubleshooting

Category | App Development

Applicable Product Version | 7FS02

Document Number | TDETS016

 

Issue

Note
This issue occurred in Tibero7 FS02 version.
When a trigger defined as AFTER UPDATE OR DELETE OR INSERT ON table is activated, calling the trigger via an UPDATE statement works correctly, but calling the trigger using a MERGE statement results in the following error.
  • (TBR-20000: TBR-10005: NOT NULL constraint violation ('SCH'.'SLES126L'.'BEGIN_YY')..)
 

Reproduction Scenario

SQL> drop table t1; drop table t2; 
SQL> create table t1 (c1 number not null, c2 number not null, c3 number not null); 
SQL> create table t2 (c1 number not null, c2 number not null, c3 number not null); 
SQL> insert into t1 values (1, 2, 3); 
SQL> commit; 
SQL> create or replace trigger trg1 after update or delete or insert on t1 for each row declare begin insert into t2 (c1, c2, c3) values (:new.c1, :new.c2, :new.c3); 
end; 
/ 

Error occurs before patch
SQL> merge into t1 using (select 1 c1 from dual) A on (t1.c1 = A.c1) when matched then update set c3 = 10;
TBR-20000: TBR-10005: NOT NULL constraint violation ('SCH'.'SLES126L'.'BEGIN_YY')..TBR-15163: Unhandled exception at SCH.TR_SLES126_AR11_1,  line 161.


Normal operation after patch applied
SQL> merge into t1 using (select 1 c1 from dual) A on (t1.c1 = A.c1) when matched then update set c3 = 10;
1 row merged.

 

 

Cause

During execution of the MERGE statement, the condition clause's value was referenced within its own query block, and while processing the parent column value in the subquery, an incorrect value was referenced causing the error. 
 
 
 
 

Solutions

Apply the patch to resolve the issue. (Applied patches: FS02_295407a, FS02_293469a)
Caution
Apply the patch through technical support provided by Tmax Tibero.
 
 
 
 
 

 

Related to