Re: Create Triggers with SQL*PLUS

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 9 Oct 2001 19:02:31 +0200
Message-ID: <ts6bde65ui8i90@news.demon.nl>

"Lothar Stärk" <lothar_at_staerk-home.de> wrote in message news:3bc2e081$0$182$4d4ebb8e_at_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
> -----------------------------------------------------
>
>

Here is the corrected script.
Please compare it
Note: I added a CR after the last /

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 users;

 ALTER TABLE Artikel
 ADD PRIMARY KEY (

        Artikel_Nr
        ) USING INDEX TABLESPACE users;

 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;
/

Hth,

Sybrand Bakker,

Senior Oracle DBA Received on Tue Oct 09 2001 - 12:02:31 CDT