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.

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?

--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 :-(

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

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

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.

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 ?

Problem found : the Statement Separator must be set to # in the SQL Datamanager.