开发者

Sybase - Syntax error when creating TRIGGER

I am getting "Incorrect syntax near 'AFTER'." when trying to create the trigger below

CREATE TRIGGER TR开发者_运维技巧_UPDATE_TEST
AFTER UPDATE OF TEST_NAME
    ON EW_TEST
REFERENCING OLD AS old_data 
NEW AS new_data
FOR EACH ROW
BEGIN
    Insert into EW_TEST_AUDIT (TEST_ID, TEST_NAME_OLD, TEST_NAME_NEW, TEST_ADDRESS_OLD, TEST_ADDRESS_NEW, TEST_MODIFIED_BY_ID, TEST_MODIFIED_DATE)
    Values(old_data.TEST_ID, old_data.TEST_NAME, new_data.TEST_NAME, old_data.TEST_ADDRESS, new_data.TEST_ADDRESS, null, sysdate());
END

Please help


This looks like a Trigger out of Sybase's SQL Anywhere product. Specifically one using the Watcom SQL Dialect.

One thing that jumps out at me would be that your missing the order statement. See your statement with the included order clause...

CREATE TRIGGER TR_UPDATE_TEST
AFTER UPDATE OF TEST_NAME ORDER 1
ON EW_TEST
REFERENCING OLD AS old_data
NEW AS new_data
FOR EACH ROW
BEGIN
     Insert into EW_TEST_AUDIT (TEST_ID, TEST_NAME_OLD, TEST_NAME_NEW, TEST_ADDRESS_OLD, TEST_ADDRESS_NEW, TEST_MODIFIED_BY_ID, TEST_MODIFIED_DATE)
     Values(old_data.TEST_ID, old_data.TEST_NAME, new_data.TEST_NAME, old_data.TEST_ADDRESS, new_data.TEST_ADDRESS, null, sysdate());
END

Granted, the ORDER clause is optional, however, not knowing what other triggers you have in this table, you could very well be looking at having more then one trigger on this table. If that's the case, then you have to use the ORDER clause.

Per the Sybase SQL Anywhere Help file...

If you omit the ORDER clause, or specify 0, the database server assigns the order of 1. However, if another same-type trigger is already set to 1, an error is returned.

When adding additional triggers, you may need to modify the existing same-type triggers for the event, depending on whether the actions of the triggers interact. If they do not interact, the new trigger must have an ORDER value higher than the existing triggers. If they do interact, you need to consider what the other triggers do, and you may need to change the order in which they fire.

Other then that, I think that everything else looks fine in your trigger. Maybe you could try adding the owner of the table before the table name? dba.EW_TEST (example)

Also, you may want to check out the new forum that Sybase put up that is similar to stackoverflow...

http://sqlanywhere-forum.sap.com/

It's a place to drop questions that are specific to the Sybase SQL Anyhwere product line.

Hope this helps you some.


It is most likely that your version of Sybase is not supporting AFTER UPDATE triggers.

I am currently facing the same issue with my version of Sybase and looking for workaround...

Best regards, vfrrjq

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜