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

mysql, phpmyadmin, triggers

Solution

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.

Problem

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.

Original source