Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a trigger which is updating a column in a different table after the row is inserted:

mysql>delimiter $$

mysql> create trigger status

 after insert on table2

 FOR EACH ROW 

 BEGIN update

 table1 inner join table2

 ON table1.id=table2.id 

 set table1.state=0 ;

Now I want to reset the table1.state=1 whenever the same value gets deleted. Is this possible?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

If I understand your question right, what you want is an after delete trigger:

DELIMITER $$
CREATE TRIGGER ad_foo AFTER DELETE on foo
FOR EACH ROW
BEGIN
  update table1 SET table1.state=1 WHERE id=OLD.id;
END$$
DELIMITER ;

Example with just the insert: http://sqlfiddle.com/#!2/974b4/1

Example with insert and delete: http://sqlfiddle.com/#!2/0bf72/1

share|improve this answer
    
thanks sir !! its working !! but i'm unable to figure out yet that what makes the difference if i do it 'before delete'... –  user21546 Apr 28 '13 at 5:45
    
if you do 'before delete' and the delete fails for some reason, you're left with the state=1 and the row still existing –  Derek Downey Apr 29 '13 at 0:11
    
ok !! thanks !! :) –  user21546 Apr 29 '13 at 8:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.