CODEWITHSUNDEEP
firebird
Roob CG
Roob CG

Reputation: 33

Token unknown -end Trigger Firebird

I tried create a trigger in Firebird ISQL tool with this syntax, but I'm getting this message error:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 6, column 1
-end

CREATE TRIGGER dataweb_inve
ACTIVE AFTER INSERT POSITION 0
ON INVE01
AS
BEGIN
  INSERT INTO dataweb (CVE_ART, DESCR, EXIST, NUM_MON, CVE_BITA) SELECT CVE_ART, DESCR, EXIST, NUM_MON, CVE_BITA FROM INVE01 ORDER BY FCH_ULTCOM desc rows 1
END;

Upvotes: 3

Views: 853

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

There are two problems with your code:

  1. Statements inside PSQL (Firebird Procedural SQL) must be terminated by a semicolon. Your insert is missing that semicolon, so the Firebird parser finds an END in an insert statement where it doesn't expect one, and raises the token unknown error.
  2. ISQL - by default - uses the semicolon to determine when a statement is complete and can be sent to the Firebird server for execution. Given PSQL uses semicolons internally to separate statements, you need to instruct ISQL to use a different statement terminator using the SET TERM command.

In short, you need to use the following in ISQL:

set term #;
CREATE TRIGGER dataweb_inve
ACTIVE AFTER INSERT POSITION 0
ON INVE01
AS
BEGIN
  INSERT INTO dataweb (CVE_ART, DESCR, EXIST, NUM_MON, CVE_BITA) 
    SELECT CVE_ART, DESCR, EXIST, NUM_MON, CVE_BITA 
    FROM INVE01 
    ORDER BY FCH_ULTCOM desc 
    rows 1;
END#
set term ;#

As an aside, the way you select the row to insert is rather suspect, as it is possible a newer row exists in INVE01 than the one the trigger fired for. If you want to insert values of the row the trigger fired for, then use the NEW context.

Your trigger would then look like:

set term #;
CREATE TRIGGER dataweb_inve
ACTIVE AFTER INSERT POSITION 0
ON INVE01
AS
BEGIN
  INSERT INTO dataweb (CVE_ART, DESCR, EXIST, NUM_MON, CVE_BITA) 
    values (new.CVE_ART, new.DESCR, new.EXIST, new.NUM_MON, new.CVE_BITA);
END#
set term ;#

Upvotes: 2

Related Questions