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

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 22 Apr 2005 17:26:43 +0100
Message-ID: <bf46380504220926f4b9713@mail.gmail.com>

On 4/21/05, Looney, Jason <Jason.Looney_at_echostar.com> 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

Received on Fri Apr 22 2005 - 12:30:56 CDT