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

From: Looney, Jason <Jason.Looney_at_echostar.com>
Date: Fri, 22 Apr 2005 10:53:58 -0600
Message-ID: <B8C9CF34B3F7164282C8610C93BB94AF0E1FE564@riv-exchb1.echostar.com>

Jared,

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.

Jason.

-----Original Message-----
From: Jared Still [mailto:jkstill_at_gmail.com] 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

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

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist



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

Received on Fri Apr 22 2005 - 12:58:16 CDT