Can I update the just added row using MySQL triggers

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?

Solution 1

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.

Solution 2

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 ;

Solution 3

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 ;

Solution 4

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 ;

View more solutions

Comments

[zerkms]

That's interesting, never seen this syntax before

[wheresrhys]

I'm now getting the following error 1442 Can't update table 'clusters' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. - any ideas why? It happens even with a very simple INSERT INTO clusters( `name`) VALUES ('qweqwe')

[zerkms]

@wheresrhys: That's because you cannot modify the row you just inserted :-)

[wheresrhys]

@zerkms - so am I right in thinking that the task I want to perform is not possible without requerying the database to get the last inserted id?

[Rene Pot]

@zerkms it's like with other languages. 1 row after an if/foreach/for, you don't need brackets. Same here :)

[zerkms]

@wheresrhys: yep, otherwise it could be very easy to get infinite loop. So it is just a protection on DB level

[wheresrhys]

how do i trigger this transaction?

[ypercubeᵀᴹ]

My point is that you can't use a trigger for this. You'll have to replace your Insert with this whole transaction (you can put this inside a stored procedure if you want).

[ypercubeᵀᴹ]

Example of how to write SPs: Transaction with Stored Procedure in MySQL Server

[wheresrhys]

@Topener I think your final edit is the best solution. It'd be handy if you could edit the just added row in MySQl triggers for INSERT as in this case I don't see much potential for an infinite loop - any changes implemented after the INSERT would be UPDATEs and so wouldn't set off the same trigger (though I can see if you allowed this behaviour with an UPDATE trigger it'd be possible to start an infinite loop)