CODEWITHSUNDEEP
mysqlsyntaxtriggers
srinidhi ts
srinidhi ts

Reputation: 11

Mysql trigger is throwing syntax errors on my trigger

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 ;

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 1

Related Questions