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

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
is
current_pk number(12);
ignore_trigger boolean := false;
end;
/

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

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) 
values(10,'marge','simpson');

10:26:07 SQL>insert into trg_test_tbl(first_name, last_name) 
values('homer','simpson');

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) 
values(10,'marge','simpson');
trigger ignored

1 row created.

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

PK FIRST_NAME LAST_NAME
---------- ------------------------------ ------------------------------
1 homer simpson
10 marge simpson

2 rows selected.

10:26:20 SQL>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Other related posts: