mysql 1303,无法从另一个存储的例程中创建触发器-什么是另一个“存储的例程”？...

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

It says:

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.

EDIT:

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.

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.

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).

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

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

The changes:

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.