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
精彩评论