Actually, depending on your jurisdiction, can hands/feet etc be classified as
weapons (professional boxers, etc?)
A before insert or update trigger is the only answer. Something like:
trigger header
ammo number;
begin
ammo := 0;
if :new.weapon_type='NONE' then
select count(*) into ammo from arrest_table
where arrest_id=:old.arrest_id
and weapon_type <> 'NONE'.
end if;
if ammo > 0 then
raise exception;
end if;
end;
In article <36669F9B.ACE0F542_at_avionics.bfg.com>,
Michael Komlev <mike_komlev_at_avionics.bfg.com> wrote:
> Hi there,
> Hope it will help.
> Mike
>
> WEAPON_ID NUMBER(6) CONSTRAINT PK_WEAPON PRIMARY KEY,
> ARREST_ID NUMBER(6) CONSTRAINT FK_WEAPON REFERENCES ARREST,
> WEAPON_TYPE VARCHAR2(6)
>
> 1) Unique constraint on all three items:
>
> ALTER TABLE weapons ADD CONSTRAINT uk_weapon UNIQUE (weapon_id, arrest_id,
> weapon_type);
>
> 2) Add check constraint:
>
> ALTER TABLE weapons
> ADD CONSTRAINT chk_weapon
> CHECK (weapon_type = 'NONE' AND weapon_id = 0 OR weapon_type <> 'NONE');
>
> 3) Provide 0 for all insert or updates with weapon_type = 'NONE'.
>
> The values in the table could be:
>
> weapon_id arrest_id weapon_type
> ---------------------------------------
> 1 1 'GUN'
> 2 1 'KNIFE'
> 3 1 'PILLOW'
> 0 2 'NONE'
> 1 3 'M-GUN'
>
>
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sat Dec 05 1998 - 00:00:00 CST