first mysql trigger failing

Question

Ok, I've started writing my first trigger in mysql, it doesn't give an errors, but it doesn't work either...

DELIMITER $$

DROP TRIGGER `cc`.`update_expires_date_trig`$$
CREATE TRIGGER `update_expires_date_trig` BEFORE INSERT ON `credit_test_acc` 
 FOR EACH ROW BEGIN
    UPDATE credit_test_acc SET date_expires_acc = DATE_ADD(CURDATE(), INTERVAL 6 MONTH) WHERE type_acc = 'init'
END;
$$
DELIMITER ;

I have 2 problems:

1. Can't update table 'credit_test_acc' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

2. Is the trigger as defined going to update JUST the JUST inserted row, or EVERY row in the database?

Answer 1

As far as I know, it must be rewritten like this to work as you expect it to work:

DELIMITER $$

DROP TRIGGER `cc`.`update_expires_date_trig`$$
CREATE TRIGGER `update_expires_date_trig` BEFORE INSERT ON `credit_test_acc` 
FOR EACH ROW BEGIN
   SET NEW.date_expires_acc = DATE_ADD(CURDATE(), INTERVAL 6 MONTH)
END;
$$
DELIMITER ;

Where NEW refers to the row that is about to be inserted into the table. You didn't give any explanation as to what role 'type_acc' might play here (I can think of more than one way it could be interpreted), so I've left that out. If it is what I think it is, you can apply it like this:

IF NEW.type_acc = 'init' THEN # do whatever you want here