Error in SQL trigger query

Question

UPDATED:

DELIMITER $$
CREATE TRIGGER updateWage BEFORE UPDATE ON st_penalty
FOR EACH ROW BEGIN
  IF DAY({fw NOW( ) } ) = 1 THEN
     UPDATE st_penalty SET st_penalty.wage = (SELECT wage FROM staff WHERE staff.memId = st_penalty.memId);
  END IF;
END$$

The error is #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 6.

No difference. The error is following "#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 'WHEN (DAY(NOW()) =1) ON st_penalty FOR EACH ROW BEGIN UPDATE st_penalty SE' at line 2" — Shnkc
– Shnkc, Commented Mar 24, 2012 at 13:25
I see no reason why the trigger should be giving #1064 error? — Abhay
– Abhay, Commented Mar 24, 2012 at 16:38

Dmitry Reznik · Accepted Answer · 2012-03-24 13:28:15Z

1

According to the syntax, there's no "WHEN" clause in MySql triggers. Also, you've missed AFTER/BEFORE INSERT/UPDATE/DELETE actions.

I'd write your trigger somewhat like this (may not compile):

CREATE TRIGGER updateWage AFTER UPDATE OR INSERT ON st_penalty
FOR EACH ROW
BEGIN
    IF (DAY(NOW()) = 1)
       UPDATE st_penalty SET st_penalty.wage = (SELECT wage FROM staff WHERE staff.memId = st_penalty.memId) WHERE st_penalty.ID = new.ID
END;

new variable contains the newly added/updated values for row. I'd recommend you reading more from the link above about writing triggers.

answered Mar 24, 2012 at 13:28

Dmitry Reznik

6,8822 gold badges34 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitry Reznik Over a year ago

put semicolon after END IF: END IF; END

Collectives™ on Stack Overflow

Error in SQL trigger query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related