Create Trigger fails

[Former Member]

Hi there,

I have a problem creating a trigger.

Normally this just works finde, but this time it fails.

We git two tables, one called buchung (german for booking) and one table holding one specific amount of data.

When executing

CREATE TRIGGER orig_trigger FOR buchung AFTER INSERT EXECUTE (

INSERT INTO ORIGINALANZAHL VALUES (:NEW.BUCHID, :NEW.ANZAHL);

)

it fails with the message:

15:44:48 [CREATE - 0 row(s), 0.000 secs] [Error Code: -5016, SQL State: 42000] [-5016] (at 128): Missing delimiter: ;

15:44:48 [) - 0 row(s), 0.000 secs] [Error Code: -3005, SQL State: 42000] [-3005]: Invalid SQL statement

... 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 2 errors]

When using

CREATE TRIGGER orig_trigger FOR buchung AFTER INSERT EXECUTE (

INSERT INTO ORIGINALANZAHL VALUES (:NEW.BUCHID, :NEW.ANZAHL)

)

(I deleted the ; at the end of the insert statement), it fails with:

15:45:09 [CREATE - 0 row(s), 0.000 secs] [Error Code: -5015, SQL State: 42000] [-5015] (at 129): Missing keyword:WITH

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

Thanks for any help,

Jan

[Former Member]

Thank you Elke! That's was exactly what was going wrong. I am using DB Visualizer. After having used the Database Studio (Using Mac, just had to install it to a unix machine) all was oaky!

Thanks again!

[Former Member]

Hi,

mhm, what kind of client are you using????

We expect to send exactly one statement to the server, but this client uses the ; to separate this single statement into 2.

(Sorry, not to have seen this earlier)

Did you use another tool for those trigger having succeeded?

Does this client has the possiblity to specify that ; should not be the statement-separator (and use // of whatever else instead)?

Elke

[Former Member]

Hi Elke,

thanks again!

Having executed this:

CREATE TRIGGER orig_trigger FOR ACCESS.buchung AFTER INSERT EXECUTE ( INSERT INTO ACCESS.ORIGINALANZAHL VALUES (:NEW.BUCHID, :NEW.ANZAHL);)

It gets me:

16:27:49 [CREATE - 0 row(s), 0.000 secs] [Error Code: -5016, SQL State: 42000] [-5016] (at 141): Missing delimiter: ;

16:27:49 [) - 0 row(s), 0.000 secs] [Error Code: -3005, SQL State: 42000] [-3005]: Invalid SQL statement

... 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 2 errors]

[Former Member]

Hi,

sorry for having been inprecise:

the ; is needed before the last closing bracket

Elke

[Former Member]

Dear Elke,

thanks for your reply!

If I execute CREATE TRIGGER orig_trigger FOR ACCESS.buchung AFTER INSERT EXECUTE ( INSERT INTO ACCESS.ORIGINALANZAHL VALUES (:NEW.BUCHID, :NEW.ANZAHL)) the error stays:

16:14:40 [CREATE - 0 row(s), 0.000 secs] [Error Code: -5015, SQL State: 42000] [-5015] (at 141): Missing keyword:WITH

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

If you like, I could give you the table structures

[Former Member]

Hi,

the error seems to be a little bit strange.

Did you check if adding the schema-name to the insert would help (or even produce a more helpful errormessage)?

Elke