Re: Modifying a constraint to make exceptions...

From: G Quesnel <dbaguy_ott_at_yahoo.com>
Date: 12 Jan 2005 11:20:14 -0800
Message-ID: <1105557614.206396.128580@c13g2000cwb.googlegroups.com>

I beleive you would need a couple of triggers to handle this situation, and to support a single statement doing updates/insert on multiple rows. The difficulty is to get around the mutating table error.

It would look something like ...
Create or replace package pkg_car as
type pls_tab_car is table of car%rowtype index by binary_integer; tmp_car pls_tab_car;
nrows binary_integer:=0;
end pkg_car;
/

Create or replace trigger TRG_CAR__BS
before INSERT or UPDATE on CAR
begin
pkg_car.nrows := 0;
end ;
/

Create or replace trigger CAR_BR
before INSERT or UPDATE on CAR
for each row
declare
v_i binary_integer;
begin
pkg_car.NROWS := pkg_car.NROWS + 1;
v_i := pkg_car.NROWS;

pkg_car.TMP_car(v_i).ID  := :new.ID;
pkg_car.TMP_car(v_i).MAKE  := :new.MAKE;
pkg_car.TMP_car(v_i).MODEL  := :new.MODEL;

end ;
/

Create or replace trigger CAR_AS
After INSERT or UPDATE on CAR
declare
v_cnt number;
v_i binary_integer;
begin
for v_i in 1..PKG_CAR.nrows loop
select count(*) into v_cnt
from car
where pkg_car.tmp_car(v_i).MAKE = MAKE
and pkg_car.tmp_car(v_i).MODEL = MODEL;
if v_cnt > 1 then
raise_application_error(-20001,'Duplicate CAR make and Model: '||
pkg_car.tmp_car(v_i).MAKE||' and '||
pkg_car.tmp_car(v_i).MODEL,FALSE);
end if;
end loop;
exception
when no_data_found then
null;
end;
/

Comments anyone (sorry for the formatting, the indentation was striped)? Received on Wed Jan 12 2005 - 13:20:14 CST