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

  • From: "Looney, Jason" <Jason.Looney@xxxxxxxxxxxx>
  • To: 'Jared Still' <jkstill@xxxxxxxxx>
  • Date: Fri, 22 Apr 2005 10:53:58 -0600


  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.  


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 [mailto:jkstill@xxxxxxxxx] 
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


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 
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 := 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');
      select trg_test_seq.nextval into trg_test_pkg.current_pk
      from dual; := 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 := true

insert into trg_test_tbl(pk,first_name, last_name)

10:26:07 SQL>insert into trg_test_tbl(first_name, last_name)

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)
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


Other related posts: