Mysql trigger is throwing syntax errors on my trigger

Question

When executed the trigger throws the error below . Please help

DELIMITER |
CREATE  TRIGGER `Problem_created` AFTER UPDATE  ON `WorkOrder_Fields` FOR EACH ROW
BEGIN
DECLARE problem_time_stamp   int;


IF   NEW.UDF_CHAR37 ='Problem'  AND  OLD.UDF_CHAR37='Incident'

    THEN

        SET problem_time_stamp    = (SELECT UNIX_TIMESTAMP(now())*1000);

        update workorder_fields set UDF_DATE6=problem_time_stamp    where WORKORDERID=OLD.WORKORDERID ;

    END IF;
END |
DELIMITER ;

========================================================================

[root@vmesx42s32 bin]#  mysql -u root -S../tmp/mysql.sock supportcenter
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 263 to server version: 4.1.18-pro

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER |
mysql> CREATE  TRIGGER `Problem_created` AFTER UPDATE  ON `WorkOrder_Fields` FOR EACH ROW
    -> BEGIN
    -> DECLARE problem_time_stamp   int;
    ->
->
    -> IF   NEW.UDF_CHAR37 ='Problem'  AND  OLD.UDF_CHAR37='Incident'

IF   NEW.UDF_CHAR37 ='Problem'  AND  OLD.UDF_CHAR37='Incident'
    ->
 -> THEN
    ->
     ->  SET problem_time_stamp    = (SELECT UNIX_TIMESTAMP(now())*1000);
 ->

        -> update workorder_fields set UDF_DATE6=problem_time_stamp    where WORKORDERID=OLD.WORKORDERID ;
 ->
    -> END IF;
 -> END |
**ERROR 1064 (42000): 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     'TRIGGER `Problem_created` AFTER UPDATE  ON `WorkOrder_Fields` FOR EACH ROW
BEGIN' at line 1**
mysql> DELIMITER ;

I just modified the trigger as suggested . Moreover i want the trigger modify only those rows who have changed from incident to problem. — srinidhi ts
– srinidhi ts, Commented Jan 26, 2015 at 16:17
Trigger was modified as suggested . But still -- ERROR 1064 (42000): 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 'TRIGGER Problem_created AFTER UPDATE ON WorkOrder_Fields FOR EACH ROW BEGIN' at line 1 — srinidhi ts
– srinidhi ts, Commented Jan 26, 2015 at 16:20

Gordon Linoff · Accepted Answer · 2015-01-25 17:35:32Z

1

You generally don't want to do an update on the table being modified by the trigger. Instead do a before insert trigger and:

IF   NEW.UDF_CHAR37 ='Problem'  AND  OLD.UDF_CHAR37='Incident'
THEN
    SET new.UDF_DATE6 := UNIX_TIMESTAMP(now())*1000);  
END IF;

This assumes that WORKORDERID is not being updated, but based on its name, that seems unlikely.

answered Jan 25, 2015 at 17:35

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Mysql trigger is throwing syntax errors on my trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related