From: "Jørgen Jamt" <JorgenJ@bgnett.no>
Subject: Triggers: IF-statements ignored?
Date: 1998/02/02
Message-ID: <6b4dvd$ga9$1@torget.bgnett.no>#1/1
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Organization: Index AS
Newsgroups: comp.databases.oracle.misc



Hi!
I'm trying to create a trigger that triggers every time a new row is
inserted into a table. The trigger is supposed to do some checks on the
inserted record, and here is my problem: It seems like my IF-statement is
ignored - nothing in the IF-part or the ELSE-part is executed when I perform
a insert-statement on the table.  The record is inserted but nothing else is
happening (I'm inserting into another table...). I have tested that the
trigger is being executed by using some RAISE-statements before the
IF-statement. The ORACLE-version I'm using is 7.0.15.6.0, but my statements
are also tested on version 7.3.2 with the same result.

I have to admit that my knowledge on triggers is pretty limited, so the
error could reside elsewhere. Please take a look at the SQL-example below.

Thank you for your time,
Jørgen Jamt,
JorgenJ@bgnett.no

CREATE TRIGGER mik_Comp_insert BEFORE INSERT ON mik_Comp REFERENCING NEW AS
Comp_Item
FOR EACH ROW DECLARE
 Mi_Comp_ID INTEGER;
 Mi_Country_ID INTEGER;
BEGIN
  SELECT countryid INTO Mi_Country_ID FROM tcountry WHERE
Upper(country)=Upper(:Comp_Item.Comp_Adr4);
  IF (Mi_Country_ID IS NULL OR SQLCODE=100) THEN
   INSERT INTO tcountry
VALUES(Mi_Country_ID,:Comp_Item.Comp_Adr4,Mi_Country_ID);
   SELECT MAX(countryid) + 1 INTO Mi_Country_ID FROM tcountry;
  END IF;

  SELECT companyid INTO Mi_Comp_ID FROM tcompany WHERE Upper(companyno) =
Upper(:Comp_Item.Comp_Code);
   IF (Mi_Comp_ID IS NULL OR SQLCODE=100) THEN
   SELECT (MAX(companyid) + 1) INTO Mi_Comp_ID FROM tcompany;
    INSERT INTO tcompany (companyid,company,companyno,countryid) VALUES
(Mi_Comp_ID,:Comp_Item.Comp_Desc,:Comp_Item.Comp_Code,Mi_Country_ID,o);
   ELSE
   UPDATE tcompany SET company=:Comp_Item.Comp_Desc,countryid=Mi_Country_ID
WHERE companyid = Mi_Comp_ID;
  END IF;
END;
/