Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: Creating Triggers and Stored Procedures using SQL-Script

    Hi,

    does anyone know how to create triggers and stored procedures using a sql-script? If I try that I'm always getting an error message like this:
    ODBC Error: SQLSTATE = S1000, Native error code = -5099 4: '<EOF>': Syntax error Driver not capable.

    I'm using Pervasive.SQL2000i Server SP4 on W2K Prof. SP4

    If I use the CREATE NEW PROCEDURE Dialog in the Control Center creating triggers ist no problem, thus the syntax is correct. It's the same with stored procedures.

    Thanks in advance.
    Last edited by ParBERTel; 08-23-04 at 06:39.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    HOw about posting your script? WHat happens if you copy the script from the Create New Procedure dialog and paste it into a new SQL DM window (where ther are no other SQL statements)? Does that work?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jun 2004
    Posts
    9

    --Script is below
    CREATE TRIGGER T9 BEFORE INSERT ON WartungErsatzKmpr
    REFERENCING NEW AS insertRow
    FOR EACH ROW

    BEGIN
    DECLARE:bz VARCHAR(75);
    DECLARE:ArtikelNr INTEGER;
    DECLARE:HerstellerNr INTEGER;

    SELECT Bezeichnung,HerstellerNr INTO :bz,:HerstellerNr FROM Artikel WHERE ArtikelNr = insertRow.ArtikelNr;

    IF NOT :bz=insertRow.Bezeichnung
    THEN SIGNAL ABORT '99999';
    END IF;

    IF NOT :HerstellerNr=insertRow.HerstellerNr
    THEN SIGNAL ABORT '99999';
    END IF;

    END;
    --


    Copy & paste between the 2 windows, as suggested, did not work :-(

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Are you trying to create a stored procedure or a trigger? You seem to have elements of both in this.
    The syntax error is occurring on line 4 which is the "BEGIN" keyword. Looking at the docs, BEGIN is not listed as being valid on a Create Trigger statement:
    Syntax
    CREATE TRIGGER trigger-name before-or-after ins-upd-del ON table-name
    [ ORDER number ]
    [ REFERENCING referencing-alias ] FOR EACH ROW
    [ WHEN proc-search-condition ] proc-stmt


    trigger-name ::= user-defined-name

    before-or-after ::= BEFORE | AFTER

    ins-upd-del ::= INSERT | UPDATE | DELETE

    referencing-alias ::= OLD [ AS ] correlation-name [ NEW [ AS ] correlation-name ]
    | NEW [ AS ] correlation-name [ OLD [ AS ] correlation-name ]


    correlation-name ::= user-defined-name
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Jun 2004
    Posts
    9
    If you have a look at the Creating Stored Procedure Section you'll find possible values for the variable proc-stmt, thus BEGIN should be a valid keyword. I think the syntax is okay as the trigger works, if I use the Create Stored Procedure Dialog.

    Any further suggestions ;-)

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Triggers and Stored Procedures are two very different entities and are written to separate places within the DDFs. When you create the trigger in the Stored Procedure Dialog, does the trigger fire on the WartungErsatzKmpr table? If it doesn't then it's not working.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Jun 2004
    Posts
    9
    From the Pervasive.SQL 2000i Doc SP4
    The CREATE TRIGGER statement creates a new trigger in a database. Triggers are a type of stored procedure that are automatically executed when data in a table is modified with an "INSERT", "UPDATE", or "DELETE". Thus it should be no problem to create triggers in the CREATE STORED PROCEDURE Dialog and in fact it is not.

    As I mentioned before the trigger fires as intended. The problem is I have to create a lot of triggers and it is really annoying to copy and paste them between my script file and the CREATE STORED PROCEDURE Dialog. It would be great to create the complete database in one step (running the script)

    Note even the examples given in documention don't work within a script try this:

    CREATE TABLE A (col1 INTEGER, col2 CHAR(10));
    CREATE TABLE B (col1 INTEGER, col2 CHAR(10)) ;
    CREATE TRIGGER MyInsert
    AFTER INSERT ON A FOR EACH ROW
    INSERT INTO B VALUES (NEW.col1, NEW.col2);


    Creating the tables is no problem, but creating the trigger fails. Perhaps there is no other way to create triggers and procedures than using the dialog ?

  8. #8
    Join Date
    Jun 2004
    Posts
    9
    Problem found : the Statement Separator must be set to # in the SQL Datamanager.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •