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

  • From: "Parker, Matthew" <matthewp@xxxxxxxxxx>
  • To: <Jason.Looney@xxxxxxxxxxxx>, "Jared Still" <jkstill@xxxxxxxxx>
  • Date: Fri, 22 Apr 2005 10:19:21 -0700

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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Looney, Jason
Sent: Friday, April 22, 2005 9:54 AM
To: 'Jared Still'
Cc: bdbafh@xxxxxxxxx; 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@xxxxxxxxx]=20
Sent: Friday, April 22, 2005 10:27 AM
To: Looney, Jason
Cc: bdbafh@xxxxxxxxx; Oracle-L
Subject: Re: brutally simple question - number of triggers on a table

=20

On 4/21/05, Looney, Jason <Jason.Looney@xxxxxxxxxxxx
<mailto: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=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;
end;
/

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



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

Other related posts: