Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Mutating trigger, using Oracle 8.1.7
Hi,
I have a mutating trigger problem I need your help with!
The problem I need to solve with the trigger is as follows:
secure that column SLINGBEN is unique within a BLOCKID. The BLOCKID column resides in a second table (not the one that the trigger is created upon), ADRBLOCK which has a 1:1 relation with the table SLINGA using SLINGID as referencing column.
The table SLINGA is created as follows:
CREATE TABLE SLINGA
(
SLINGID NUMBER NOT NULL, DISTSATTID NUMBER NOT NULL, UTFORID NUMBER NOT NULL, FARDTILL NUMBER(3), FARDINOM NUMBER(3), FARDFRAN NUMBER(3), MODULJN VARCHAR2(1 BYTE) NOT NULL, SLINGBEN VARCHAR2(35 BYTE) NOT NULL, REGANVID VARCHAR2(10 BYTE) NOT NULL, REGDATID DATE NOT NULL,INFORMATION VARCHAR2(40 BYTE)
My trigger looks like:
CREATE OR REPLACE TRIGGER slinga_unik_ben_iu_trig
BEFORE INSERT OR UPDATE
ON SLINGA
FOR EACH ROW
DECLARE
n_c NUMBER; n_blockid NUMBER;BEGIN
SELECT COUNT (1) INTO n_c FROM VY_SLINGA WHERE VY_SLINGA.slingben = :NEW.slingben AND VY_SLINGA.slingid IN (SELECT slingid FROM MODUL WHEREMODUL.blockid = n_blockid);
IF n_c > 0 THEN RAISE_APPLICATION_ERROR (-20001, 'Verksamhetsnyckel constraint överträdd i tabell SLINGA. SLINGBEN ej unikt inom adressblock!'); END IF;
I'm looking forward to look at your responses on this problem ASAP, best regards Peter Received on Wed May 24 2006 - 07:08:56 CDT