Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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 <matthewp_at_amazon.com>
Date: Fri, 22 Apr 2005 10:19:21 -0700
Message-ID: <F385925F530F6C4081F1659F71640EB3042D9361@ex-mail-sea-04.ant.amazon.com>


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

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 22 2005 - 13:27:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US