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.

seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question:
I have three tables or more, refer to those as A,B,C. C holds some columns and A & B refer to those columns.
A & B data can be deleted -> and here comes my question:
I'd like the referential to go as when there are no valid references to C, the referenced row will get deleted, since it's not in use. I thought of adding an auto_increment to C, and make A & B refer to the index, and just keep multiple of the same data, and just delete the attached. This hardly seems like the most reasonable solution. If anyone could point some light on my problem, that would be great.
Thank you.

EDIT: I'm using SQLite.

share|improve this question
    
You are looking for cascading delete, maybe triggers. Which RDBMS are you using? –  vonPryz Nov 11 '13 at 13:59
    
cascading delete will delete the reference on the first delete no? Since I want to have multiple references to the same row, I don't want it to be deleted on the first delete. –  user2558461 Nov 11 '13 at 14:00
    
Also making sure I stated correctly. I want to make my logic as when I delete the reference, the actual referenced row will be deleted, this is the opposite of cascade as far as I know. –  user2558461 Nov 11 '13 at 14:07

1 Answer 1

up vote 0 down vote accepted

I would implement it using a reference counter:

CREATE TRIGGER new_in_b AFTER INSERT ON B
FOR EACH ROW 
BEGIN
  UPDATE A SET refcounter = refcounter + 1 WHERE id = new.foreignkey;
END;

CREATE TRIGGER remove_from_b AFTER DELETE ON B
FOR EACH ROW 
BEGIN
  UPDATE A SET refcounter = refcounter - 1 WHERE id = old.foreignkey;
  DELETE FROM A WHERE id = old.foreignkey AND refcounter = 0;
END;
share|improve this answer
    
interesting, this seems like what I wanted. but is it possible to have the same Trigger for multiple tables? I need those triggers to be on two tables (A&B when C is the referenced) –  user2558461 Nov 11 '13 at 15:00
    
Yes, that is indeed possible. –  Twinkles Nov 11 '13 at 15:04
    
I need to make four triggers with the same code? or can I apply same trigger to multiple tables? –  user2558461 Nov 11 '13 at 15:10
    
Of course, a trigger only works on exactly ONE table, so you will need to define multiple triggers. –  Twinkles Nov 11 '13 at 15:10

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.