Create Triggers with SQL*PLUS

From: Lothar Stärk <lothar_at_staerk-home.de>
Date: Tue, 9 Oct 2001 13:34:59 +0200
Message-ID: <3bc2e081$0$182$4d4ebb8e@read.news.de.uu.net>

Hi all,

i have a problem on creating Triggers in a long sql-script. For example:
I want to create a table, a primary key, a sequence and a Trigger. if I start the script, everything will be created, but not the trigger. if I try to create this trigger in a separate statement the trigger will be created.

Here is the sql-script:

DROP TABLE Artikel CASCADE CONSTRAINTS;
 CREATE TABLE Artikel

          Artikel_Nr  NUMBER (11,0)  NULL ,
         Artikelname  VARCHAR2 (40)  NOT NULL ,
         Lieferanten_Nr  NUMBER (11,0)  NULL ,
         Kategorie_Nr  NUMBER (11,0)  NULL ,
         Liefereinheit  VARCHAR2 (25)  NULL ,
         Einzelpreis  NUMBER (15,2)  NULL ,
         Lagerbestand  NUMBER (5,0)  NULL ,
         BestellteEinheiten  NUMBER (5,0)  NULL ,
         Mindestbestand  NUMBER (5,0)  NULL ,
         Auslaufartikel  NUMBER (1,0)  NULL

 ) TABLESPACE nwind;

 ALTER TABLE Artikel
 ADD PRIMARY KEY

        Artikel_Nr
        ) USING INDEX TABLESPACE nwind;

 DROP SEQUENCE SEQ_Artikel;
 CREATE SEQUENCE SEQ_Artikel
 INCREMENT BY 1 START WITH 1;
 CREATE OR REPLACE TRIGGER TRG_Artikel
 BEFORE INSERT OR UPDATE ON Artikel
 FOR EACH ROW
 DECLARE
 cannot_change_counter EXCEPTION;
 BEGIN
 IF INSERTING THEN
 SELECT SEQ_Artikel.NEXTVAL INTO :NEW.Artikel_Nr FROM DUAL;  END IF;
 IF UPDATING THEN
 IF NOT (:new.Artikel_Nr = :old.Artikel_Nr) THEN  RAISE cannot_change_counter;
 END IF;
 END IF;
 EXCEPTION
 WHEN cannot_change_counter THEN
 raise_application_error(-2000,'Cannot Change Counter Value');  END;
 /

when I start this script in the SQL-Worksheet, there is no problem. but in this special
case I have to execute this script in sqlplus.

Thanks for your help.

Lothar

--
------------------------------------------------------
Lothar Staerk
Haringstr. 29
85635 Siegertsbrunn
Phone: ++49(8102)748882
E-Mail: lothar_at_staerk-home.de
-----------------------------------------------------

Received on Tue Oct 09 2001 - 06:34:59 CDT