ROW level and Statement Level Database Triggers Difference with Example

---Sequences and Tables
CREATE SEQUENCE XX_MAIN_TGR_TBL_S
  START WITH 1
  MAXVALUE 999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;
 
 
  CREATE SEQUENCE XX_INSERT_TBL_S
  START WITH 90000000
  MAXVALUE 999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

create table xx_main_tgr_tbl
(
PK_ID number,
NOTE varchar2(400),
creation_date date,
las_update_date date
);

create table xx_insert_history_tbl
(
insert_ID number,
NOTE varchar2(400),
creation_date date,
las_update_date date
);
----------------------------------------------------
---Triggers all
  create or replace trigger xx_main_tbl_rowtrg
after insert On xx_main_tgr_tbl for each row
declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into xx_insert_history_tbl
values(XX_INSERT_TBL_S.nextval,
'xx_main_tbl_rowtrg after insert for each row',
sysdate,
sysdate
);

commit;

end;


  create or replace trigger xx_main_tbl_stmenttrg_aftr
after insert On xx_main_tgr_tbl
declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into xx_insert_history_tbl
values(XX_INSERT_TBL_S.nextval,
'xx_main_tbl_stmenttrg_aftr after insert statement',
sysdate,
sysdate
);

commit;

end;

  create or replace trigger xx_main_tbl_rowtrg_bfr
before insert On xx_main_tgr_tbl for each row
declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into xx_insert_history_tbl
values(XX_INSERT_TBL_S.nextval,
'xx_main_tbl_rowtrg_bfr before insert for each row',
sysdate,
sysdate
);

commit;

end;

   create or replace trigger xx_main_tbl_stmnttrg_bfr
before insert On xx_main_tgr_tbl
declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into xx_insert_history_tbl
values(XX_INSERT_TBL_S.nextval,
'xx_main_tbl_stmnttrg_bfr before insert for statement level',
sysdate,
sysdate
);

commit;

end;

  create or replace trigger xx_main_tbl_rowtrg_Aupd
after update On xx_main_tgr_tbl for each row
declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into xx_insert_history_tbl
values(XX_INSERT_TBL_S.nextval,
'xx_main_tbl_rowtrg_Aupd after update for each row',
sysdate,
sysdate
);

commit;

end;

 create or replace trigger xx_main_tbl_stmtrg_Aupd
after update On xx_main_tgr_tbl
declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into xx_insert_history_tbl
values(XX_INSERT_TBL_S.nextval,
'xx_main_tbl_stmtrg_Aupd after update statement level',
sysdate,
sysdate
);

commit;

end;
-----------------------
---MainTable_insert Script
declare


begin


for i in 11..14 loop
insert into xx_main_tgr_tbl
values
(XX_MAIN_TGR_TBL_S.nextval,
'XX_MAIN_TGR_TBL insert 4 lines bulk - '||i,
sysdate,
sysdate
);
commit;
end loop;
--commit;

end;
------------
--update main table script
update xx_main_tgr_tbl
set LAS_UPDATE_DATE=sysdate

commit;
----

--check the values and sequence

select * from xx_main_tgr_tbl

select * from xx_insert_history_tbl
------------------------------------------------------

Hello World

begin
dbms_output.put_line(' Hello World ');
end;