Sybase - 创建 TRIGGER 时出现语法错误

发布于 2024-10-20 13:17:36 字数 497 浏览 0 评论 0原文

我收到“‘AFTER’附近的语法不正确。”当尝试创建下面的触发器时

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

请帮忙

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

呆 2024-10-27 13:17:36

这看起来像是 Sybase 的 SQL Anywhere 产品中的触发器。具体来说，一种是使用 Watcom SQL Dialect。

让我惊讶的一件事是你错过了订单声明。请参阅包含 order 子句的语句...

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

当然，ORDER 子句是可选的，但是，如果不知道此表中还有哪些其他触发器，您很可能会考虑此表上有多个触发器。如果是这种情况，那么您必须使用 ORDER 子句。

根据 Sybase SQL Anywhere 帮助文件...

如果省略 ORDER 子句或指定 0，数据库服务器将分配顺序 1。但是，如果另一个相同类型的触发器已设置为 1，则会出现错误

添加其他触发器时，您可能需要修改事件的现有同类型触发器，具体取决于触发器的操作是否交互。如果它们不交互，则新触发器的 ORDER 值必须高于现有触发器。如果它们确实相互作用，您需要考虑其他触发器的作用，并且可能需要更改它们触发的顺序。

除此之外，我认为触发器中的其他一切看起来都很好。也许您可以尝试在表名之前添加表的所有者？ dba.EW_TEST（示例）

另外，您可能想查看 Sybase 推出的类似于 stackoverflow 的新论坛...

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

这是一个提出特定于 Sybase SQL Anyhwere 产品线的问题的地方。

希望这对您有所帮助。

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.

回复收藏 0 原文