CODEWITHSUNDEEP
mysqlsql
Mike
Mike

Reputation: 1580

MySQL Trigger not saving

Executing this:

CREATE TRIGGER `after_order_insert`
    AFTER INSERT ON `hb_orders` FOR EACH ROW
    BEGIN
       UPDATE hb_accounts
       SET hb_accounts.domain = (SELECT companyname FROM hb_client_details
                     WHERE hb_client_details.id = NEW.client_id
                            LIMIT 1)
       WHERE hb_accounts.client_id = NEW.client_id;
    END

Results in this:

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8 */

What am I missing? This should work, shouldn't it?

Thanks

Upvotes: 1

Views: 180

Answers (2)

Fabricator
Fabricator

Reputation: 12772

Here's another implementation:

CREATE TRIGGER `after_order_insert`
AFTER INSERT ON `hb_orders` FOR EACH ROW
UPDATE hb_accounts a
join hb_client_details b on a.client_id = b.id and b.id = new.client_id
set a.domain = b.companyname;

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You're most likely trying to add the trigger without changing the delimiter. Since the trigger contains semicolon, you'll have to change the delimiter to something else temporarily;

DELIMITER //

CREATE TRIGGER `after_order_insert`
    AFTER INSERT ON `hb_orders` FOR EACH ROW
    BEGIN
       UPDATE hb_accounts
       SET hb_accounts.domain = (SELECT companyname FROM hb_client_details
                     WHERE hb_client_details.id = NEW.client_id
                            LIMIT 1)
       WHERE hb_accounts.client_id = NEW.client_id;
    END //

DELIMITER ;

An SQLfiddle with the trigger adding successfully. Note that the delimiter is changed in the settings,

Upvotes: 2

Related Questions