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
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
