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