| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: brutally simple question - number of triggers on a table
Actually we use something similar and only one package is necessary. As =
a matter of fact any package that exposes a global variable that you can =
set and examine in your session is all that is needed. The global =
variable has a default value that would mean the trigger always fires, =
when examining that variable and only does not fire when the variable =
has been set opposite it's default value. It works on very large scale =
systems with millions of transactions an hour.=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Looney, Jason
Sent: Friday, April 22, 2005 9:54 AM
To: 'Jared Still'
Cc: bdbafh_at_gmail.com; Oracle-L
Subject: RE: brutally simple question - number of triggers on a table
Jared,=20
=20
In your example below, you would need to have one of these procedures =
for
every trigger in the database. This would get complicated and difficult =
to
maintain. I can see many ways to do it, a single package for all =
triggers,
a package for each trigger, a single piece of code with a string passed =
in
for the trigger name where you could check based on that string. Each =
of
these has problems to work around, but they are not impossible to do. =20
=20
Triggers do have some benefits especially database login triggers, and
auditing of DML, but most of these things are not application events, =
and
therefore could not be put into a common code base.
=20
I guess my point was if you have the luxury to design an application, =
design
it without the triggers and your life becomes easier. If you don't have
that luxury, and your environment is such that you don't have a common =
code
base, then you may be forced to use triggers to solve a particular =
problem.
I would hesitate to use them instead of changing the application, and =
would
try to come up with alternate solutions, but at the end of the day the
functionality is there for a reason. I just think many people abuse =
them.
=20
Jason.
=20
-----Original Message-----
From: Jared Still [mailto:jkstill_at_gmail.com]=20
Sent: Friday, April 22, 2005 10:27 AM
To: Looney, Jason
Cc: bdbafh_at_gmail.com; Oracle-L
Subject: Re: brutally simple question - number of triggers on a table
=20
On 4/21/05, Looney, Jason <Jason.Looney_at_echostar.com <mailto: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=20
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 :=3D 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 :=3D 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 :=3D 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 :=3D 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>
--=20
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 22 2005 - 13:27:00 CDT
 |
 |