Can't create a TRIGGER from within another stored routine -- What is another "stored routine"?

Question

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.

Answer 1

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.