CODEWITHSUNDEEP
mysqltriggers
bbutle01
bbutle01

Reputation: 521

first mysql trigger failing

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?

Upvotes: 0

Views: 362

Answers (2)

Johan
Johan

Reputation: 76641

A Trigger cannot change the table that triggered it.
Either directly or indirectly.

You can only change values in a BEFORE trigger by SET new.field = newvalue.
And this can only effect the 'current' row that pulled the trigger (so to speak).

Upvotes: 0

shylent
shylent

Reputation: 10086

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

Upvotes: 1

Related Questions