mysql Trigger On insert

Question

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

can you show the compilation error message?

heximal
– heximal

2011-08-11 14:47:40 +00:00
Commented Aug 11, 2011 at 14:47 — heximal
– heximal, Commented Aug 11, 2011 at 14:47

Johan · Accepted Answer · 2011-08-11 20:39:03Z

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

Mchl · Accepted Answer · 2011-08-11 15:19:16Z

2

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;

edited Aug 11, 2011 at 15:19

answered Aug 11, 2011 at 14:51

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

3 Comments

Mchl Over a year ago

perhaps tell us what you want to achieve then?

Tony77 Over a year ago

I have two tables, one is called userprofile the other is called users Everytime a new record is inserted into userprofile I want to update the table 'users' this a mockup structure table -userprfile id uid name department table-users id name email department ( want to update this field on every insert from the userprofiletable) As now , the table userprofile matches users on uid = id , there is no contratins, or foreign keys, which I probably would need, not sure,,, How can I do this.? Thanks in advance.....

Mchl Over a year ago

If you're running this from phpMyAdmin sl box, note that there's a separate field for delimiter below. In such case just put || there and omit DELIMITER statements from the SQL above

Collectives™ on Stack Overflow

mysql Trigger On insert

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related