Re: brutally simple question - number of triggers on a table

  • From: Jared Still <jkstill@xxxxxxxxx>
  • To: Jason.Looney@xxxxxxxxxxxx
  • Date: Fri, 22 Apr 2005 17:26:43 +0100

On 4/21/05, Looney, Jason <Jason.Looney@xxxxxxxxxxxx> wrote:
> It depends on your requirements. I have always felt that if you have an
> environment, where you need to have high availability, then triggers are 
> not
> for you. The fact is that when you modify a trigger or need to disable the
> trigger for mass updates it requires a downtime for the entire 
> application.

Not necessarily.

drop table trg_test_tbl;
drop sequence trg_test_seq;

create sequence trg_test_seq
start with 1

create table trg_test_tbl (
pk number(12) not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null

create or replace package trg_test_pkg
current_pk number(12);
ignore_trigger boolean := false;

create or replace trigger trg_test_trg
before insert on trg_test_tbl
for each row
if trg_test_pkg.ignore_trigger then
dbms_output.put_line('trigger ignored');
select trg_test_seq.nextval into trg_test_pkg.current_pk
from dual; := trg_test_pkg.current_pk;
end if;

insert into trg_test_tbl(first_name, last_name) values('homer','simpson');

exec trg_test_pkg.ignore_trigger := true

insert into trg_test_tbl(pk,first_name, last_name) 

10:26:07 SQL>insert into trg_test_tbl(first_name, last_name) 

1 row created.

10:26:07 SQL>
10:26:07 SQL>exec trg_test_pkg.ignore_trigger := true

PL/SQL procedure successfully completed.

10:26:07 SQL>
10:26:07 SQL>insert into trg_test_tbl(pk,first_name, last_name) 
trigger ignored

1 row created.

10:26:07 SQL>
10:26:07 SQL>select * from trg_test_tbl;

---------- ------------------------------ ------------------------------
1 homer simpson
10 marge simpson

2 rows selected.

10:26:20 SQL>

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


Other related posts: