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

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

From: Parker, Matthew <>
Date: Fri, 22 Apr 2005 10:19:21 -0700
Message-ID: <>

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: =
[] On Behalf Of Looney, Jason Sent: Friday, April 22, 2005 9:54 AM
To: 'Jared Still'
Cc:; Oracle-L
Subject: RE: brutally simple question - number of triggers on a table


  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


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.


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.




-----Original Message-----
From: Jared Still []=20 Sent: Friday, April 22, 2005 10:27 AM
To: Looney, Jason
Cc:; Oracle-L
Subject: Re: brutally simple question - number of triggers on a table


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

   current_pk number(12);
   ignore_trigger boolean :=3D false;

create or replace trigger trg_test_trg
before insert on trg_test_tbl
for each row

   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; :=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

Received on Fri Apr 22 2005 - 13:27:00 CDT

Original text of this message