-
Row level before trigger processing multiple rows and not just the current one
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
------------------
What am I doing wrong. Why it is inserting 3 records when it should only 1)? Please advice. Thanks
-----------------
I have a before trigger that is processing (inserting in this case) multiple previous rows along with the current one. To debug, I made it very basic one but it is still doing the same thing.
Z_table.message_id is the primary key.
Please see the trigger code below:
------------------
create table Z_table_copy as select * from z_table where 1=2;
truncate table Z_table_copy ;
create or replace trigger tr_new
before insert or update or delete
on z_table
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
when ( NEW.group_code = 'abc')
declare
v_group_id integer;
begin
Insert into Z_table_copy (message_id, change_id, group_code, comments)
Values(:NEW.message_id, :NEW.change_id, :NEW.group_code, :NEW.comments);
end;
---------------------------------
insert into Z_table Values ('aa', 1, 'abc', 'mycomment')
Select count(*) from Z_table_copy ;
count(*)
----------
3
Last edited by daljitsb; 07-25-2011 at 04:31 PM.
-
Looks like some kid of Oracle Bug that caused row level trigger to get confused.
The trigger was originally PRAGMA AUTONOMOUS_TRANSACTION
and there commits and rollbacks within trigger and called-in stored proc. That probably confused the trigger or database.
It was processing multiple previous table rows inserts along with the current row getting inserted even when I removed AUTONOMOUS_TRANSACTION part from the trigger.
Dropping and recreating the same trigger was not even fixing the issue but then deleting those rows and creating very basic new trigger eventually resolved the issue.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|