无法从另一个存储的例程中创建触发器-什么是另一个“存储的例程”？ [英] Can't create a TRIGGER from within another stored routine -- What is another "stored routine"?

问题描述

我正在尝试使用PhpMyAdmin在MySQL中创建触发器。

I'm trying to create a trigger in MySQL using PhpMyAdmin.

CREATE TRIGGER Update_Last_Transit_Status AFTER INSERT ON Delivery 
FOR EACH ROW
BEGIN
UPDATE Transportation SET Status="Dispatched" WHERE 
EXISTS (SELECT * FROM Transit, Route WHERE Transit.ID=Route.Transit_ID and
        Route.Delivery_ID=Delivery.ID and 
        Transit.Transportation_ID=Transportation.ID) and
        Status="In Branch"
END

它说：

MySQL said: #1303 - Can't create a TRIGGER from within another stored routine

我知道此错误已得到解决，但这不是我的

I know this error has been addressed before, but this wasn't my intention at all.

这里的另一个存储例程在哪里？

Where is "another stored routine" here?

我不打算做什么错误消息说。

I don't intend to what the error message says.

编辑：
没有定义其他触发器。但是，有一个预定义的过程：

There are no other triggers defined. There is however one procedure predefined:

begin
        select user() as first_col;
        select user() as first_col, now() as second_col;
        select user() as first_col, now() as second_col, now() as third_col;
        end

我不知道它的作用，或者为什么存在，但是

I don't know what it does, or why it is there, but it was there before.

推荐答案

上面显示的触发器很好。

The trigger you show above is fine.

编辑：在phpMyAdmin的GUI中创建触发器时，只需在中输入触发器的主体定义窗格，换句话说，是 BEGIN ... END 部分。

edit: When you create a trigger in the GUI of phpMyAdmin, you only need to enter the body of the trigger in the Definition pane, in other words the part BEGIN...END.

这是因为phpMyAdmin会变得更聪明，并根据您输入的其他元素（名称，表，时间，事件）为您编写触发器标头。

This is because phpMyAdmin is going to try to be clever and write the trigger header for you based on the other elements you enter (name, table, time, event).

这是在phpMyAdmin中定义触发器的正确​​方法：

Here's the right way to define a trigger in phpMyAdmin:

如果您编写 CREATE TRIGGER .. 标头，它会混淆MySQL，因为它将看到 CREATE TRIGGER ... CREATE TRIGGER ... BEGIN ... END 。这使MySQL认为您正在定义第一个语句为 CREATE TRIGGER 的触发器。

If you write the CREATE TRIGGER... header inside the body, it will confuse MySQL because it'll see CREATE TRIGGER... CREATE TRIGGER... BEGIN...END. This makes MySQL think you are defining a trigger whose first statement is CREATE TRIGGER.

作为您原始问题的附带问题，我建议对触发器的主体进行一些更改：

As a side issue from your original question, I'd suggest some changes in the body of the trigger:

CREATE TRIGGER Update_Last_Transit_Status AFTER INSERT ON Delivery 
FOR EACH ROW
BEGIN
  UPDATE Transportation
    INNER JOIN Transit ON Transit.Transportation_ID = Transportation.ID
    INNER JOIN Route ON Transit.ID = Route.Transit_ID
  SET Transportation.Status = 'Dispatched'
  WHERE Route.Delivery_ID = NEW.ID
    AND Transportation.Status = 'In Branch';
END

更改：

• 引用 NEW.ID 代替 Delivery.ID 。


• 使用SQL-92 JOIN 语法代替SQL-89逗号样式联接。


• 使用多具有联接的表 UPDATE ，而不是具有相关子查询的 EXISTS 。


• 使用


• 用分号终止 UPDATE 语句。

• Reference NEW.ID instead of Delivery.ID.
• Use SQL-92 JOIN syntax instead of SQL-89 "comma style" joins.
• Use multi-table UPDATE with joins, instead of EXISTS with correlated subquery.
• Use single-quotes for strings instead of double-quotes.
• Terminate the UPDATE statement with a semicolon.

这篇关于无法从另一个存储的例程中创建触发器-什么是另一个“存储的例程”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！