Can I update the just added row using MySQL trigge

Question

The default initial value of one column in my database is the same as the row's auto-incremented id. I'm trying to use triggers to set it.

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
BEGIN  
    UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id; 
END

But this keeps throwing a syntax error

#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 5

I've tried all sorts of permutations of this with no luck. Can anyone see what I'm doing wrong?

Answer 1

You can create just BEFORE INSERT TRIGGER, it's works like this:

CREATE TRIGGER `default_order_value` 
    BeFORE INSERT ON `clusters` 
    FOR EACH ROW  
    BEGIN  

    SET NEW.`order` = NEW.id ;
    END

same as below we are using

DELIMITER $$

USE `e_store`$$

DROP TRIGGER /*!50032 IF EXISTS */ `Test`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `Test` BEFORE INSERT ON `categories` 
    FOR EACH ROW 
BEGIN
   DECLARE vtype   VARCHAR(250) DEFAULT NULL;
   SET vtype = NEW.name;
   IF (NEW.MDNAME IS NULL)
   THEN
    --  SET NEW.MDNAME = 'NA';
   SET NEW.MDNAME=MD5(NEW.name);
   END IF;
END;
$$

DELIMITER ;

Answer 2

This worked for me:

CREATE TRIGGER `update_table_2`
AFTER UPDATE ON `table_1`
FOR EACH ROW
UPDATE table2
JOIN table_1
SET table_2.the_column = NEW.the_column
WHERE table_2.auto_increment_field = OLD.auto_increment_field

Answer 3

As zerkms said, you need to change the delimeter. But since you only use 1 line of code, you don't need the BEGIN and END. And that way, you don't need to change the delimiter either

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
    UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id; 

Since you are getting an error you cannot update the row, I suggest the following:

Do NOT perform the update query at all. On default the order value = the ID value. So when the order value changes, you can update it properly.

If you are requesting the data with php, do something like this:

$order = $row['order'];
if ($order == '')
    $order = $row['id'];

After you need it updating, you've got the correct value.

Answer 4

I don't think you can do that. An AFTER INSERT trigger cannot modify the same table, neither by issuing an UPDATE nor by something like this:

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
    SET NEW.`order` = NEW.id ; 

which results in this error:

> Error Code: 1362. Updating of NEW row is not allowed in after trigger

---

You can't either use a BEFORE INSERT trigger because then the NEW.id is not known (if you modify the above, the order column will get 0 value after the Insert.

---

What you can do, is use a transaction:

START TRANSACTION ;
  INSERT INTO clusters (id)
    VALUES (NULL);
  UPDATE clusters
    SET `order` = id
    WHERE id = LAST_INSERT_ID();
COMMIT ;

Answer 5

You get the error because mysql treats ; in line 5 as the end of your trigger declaration, which obviously leads to the syntax error.

So you need to redefine delimiter before you specify the trigger body:

delimiter |

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
BEGIN  
    UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id; 
END;
|

delimiter ;