First mysql trigger
Ok, I started writing my first trigger in mysql, it gives no 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:
-
Cannot update table "credit_test_acc" in stored function / trigger because it is already in use by the operator that called this stored function / trigger.
-
Is the trigger as defined to JUST JUST update the inserted row or EVERY row in the database?
0
source to share
2 answers
As far as I know, it should be rewritten in such a way that it works how 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 to be inserted into the table. You haven't given any explanation as to what role "type_acc" can play here (I can think of more than one way this could be interpreted), so I left that. If this is what I think you can apply it like this:
IF NEW.type_acc = 'init' THEN # do whatever you want here
+1
source to share