Re: Mutating triggers

From: <tkyte_at_us.oracle.com>
Date: 1997/01/23
Message-ID: <854074269.30310@dejanews.com>#1/1

In article <32E5374E.2ECB_at_lilly.com>,
Chris Halioris <halioris_chris_nonlilly_at_lilly.com> wrote:
>
> I have a table that has a conditional unique constraint on it.
> Assume I have two columns: parm_code and code_value.
> If parm_code = 'A' then the code_value must be unique for all rows with
> parm_code='A'.
> If parm_code != 'A' then the code value does not have to be unique.
> Hence, I cannot create a unique constraint on parm_code and code_value.
>
> Therefore I created a database trigger to check if parm_code = 'A' then
> see if there are any other records with parm_code = 'A' and code_value =
> :new.code_value (and if there is I raise_application_error). The trigger
> works fine for an insert but mutates on an update. Is there a trick to
> doing something like this, or a better way?
>

I would suggest something like this:

create sequence T_seq
/

create table T_tbl
( .....

    parm_code char(1),
    code_value varchar2(...),
    EXTRA number default NULL
)
/

create view T as select (everything BUT extra from T_tbl) /
grant (whatever access needed) on T (to whomever) /

alter table T add constraint T_unique unique( parm_code, code_value, EXTRA ) /

create or replace trigger T_trigger
before insert or update on T
for each row
begin

if ( updating ) then

      if ( :new.parm_code = 'A' ) then
         :new.EXTRA := NULL;
      elsif ( :new.EXTRA is NULL ) then
         select T_seq.nextval into :new.EXTRA from dual;
      end if;
   elsif ( :new.parm_code <> 'A' ) then
      select T_seq.nextval into :new.EXTRA from dual;

end if;
end;
/

this way, the unique constraint will be:

if parm_code = A, parm_code & code_value must be unique (since EXTRA is always NULL) if parm_code <> A, parm_code & code_value & EXTRA must be unique (actually, they will always be unique since EXTRA is based on a sequence).

> Chris Halioris

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com

http://govt.us.oracle.com

Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Thu Jan 23 1997 - 00:00:00 CST