Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Mutating trigger, using Oracle 8.1.7

Mutating trigger, using Oracle 8.1.7

From: <Peter.Williamsson_at_gmail.com>
Date: 24 May 2006 05:08:56 -0700
Message-ID: <1148472536.332234.302140@g10g2000cwb.googlegroups.com>


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
 IF :NEW.moduljn = 'J' THEN
       SELECT COUNT (1)
       INTO   n_c
       FROM   VY_SLINGA
       WHERE  VY_SLINGA.slingben = :NEW.slingben
     AND    VY_SLINGA.slingid IN (SELECT slingid FROM MODUL WHERE
MODUL.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;

   END IF;
END;
/

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US