Monday, August 13, 2012

Sample script to test OGG "DBOPTIONS SUPPRESSTRIGGERS"

create table hr.tbl_trigger_test
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_pk primary key(id))
;

create or replace trigger hr.trg_trigger_test
before insert on hr.tbl_trigger_test
for each row
begin
:new.updated_host:=sys_context('userenv','host');
:new.updated_user:=sys_context('userenv','session_user');
:new.updated_time:=systimestamp;
end;
/

create table hr.tbl_trigger_test_2
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_2_pk primary key(id))
;

create or replace trigger hr.trg_trigger_test_2
after insert on hr.tbl_trigger_test
REFERENCING NEW AS newRow
for each row
begin
   insert into hr.tbl_trigger_test_2 values(
 :newRow.id,:newRow.updated_host,:newRow.updated_user,:newRow.updated_time);
end;
/


insert into hr.tbl_trigger_test values ('1',null,null,null);
insert into hr.tbl_trigger_test values ('2',null,null,null);
insert into hr.tbl_trigger_test values ('3',null,null,null);
insert into hr.tbl_trigger_test values ('4',null,null,null);
insert into hr.tbl_trigger_test values ('5',null,null,null);
commit;

drop trigger hr.trg_trigger_test_2;
drop trigger hr.trg_trigger_test;
drop table hr.tbl_trigger_test_2 purge;
drop table hr.tbl_trigger_test purge;