CODEWITHSUNDEEP
phpmysqltriggers
Tony77
Tony77

Reputation: 311

mysql Trigger On insert

whats wrong with my syntax?

CREATE 
    TRIGGER db_dhruniversity.trigger1
    AFTER INSERT
    ON jos_dhruprofile
    FOR EACH ROW
BEGIN
UPDATE jos_users 
  SET jos_users.department = jos_dhruprofile.department 
  WHERE jos_users.id = jos_dhruprofile.uid
END

Upvotes: 2

Views: 701

Answers (2)

Johan
Johan

Reputation: 76567

The syntax should be as follows:

DELIMITER $$  /* if you're not using an editor, you must change the delimiter*/

CREATE 
    TRIGGER ai_jos_dhruprofile_each
    AFTER INSERT
    ON jos_dhruprofile
    FOR EACH ROW
BEGIN
  UPDATE jos_users 
  SET jos_users.department = NEW.department 
  WHERE jos_users.id = NEW.uid;  /*<<<--- ; after every stament */
END $$   /* changed delimiter after the end */

DELIMITER ; /*make sure you set the delimiter back to the default*/

Note on the naming scheme for triggers
I'd recommend naming your trigger ai (meaning after insert) so you know when it fires on which table, rather than a meaningless name like: db_dhruniversity.trigger1.
I always use [a/b]+[d/i/u]_tablename_each as the triggername, that way I always know when the triggers fires (before/after) for which event (insert/delete/update) and on which table.

It's also good practise to document that the trigger fires on each row, hence the each on the end of the trigger name.

Note that MySQL does not support triggers that fire once per statement yet (But that might change in future).

Upvotes: 4

Mchl
Mchl

Reputation: 62387

There are no delimiters in it:

DELIMITER ||

CREATE 
    TRIGGER db_dhruniversity.trigger1
    AFTER INSERT
    ON jos_dhruprofile
    FOR EACH ROW
BEGIN
UPDATE jos_users 
  SET jos_users.department = NEW.department 
  WHERE jos_users.id = NEW.uid;
END ||

DELIMITER;

Upvotes: 2

Related Questions