I have a existing database with several tables, unfortunately there ins't any data load functionality provided for said database and I am trying to rectify this issue.
I have an insert function that processes and inserts the data we need as needed into the relevant tables and sets up the interlinking fkeys that link all the new data together.
the insert statement is set up to return the new primary keys of all the new rows its created a records table to allow rollback. What I want to do is set up a function where if a row in the records table is deleted it will also delete all its referencing rows in the rest of the database (without having to change all the other tables)
example : http://rextester.com/SKJUJ87661
basically when I run
delete from t where tid = 1; I want corresponding data in t(upload records) and u(tables where data was created) to be deleted and v(existing reference tables) untouched
and yes, I know I can just run
delete from u where uid = (select t.uid from t where t=1); for the relevant tables, but I'm looking for a more elegant solution then writing a massive delete statement (in the actual system t references 18 rows)
using triggers i can do this http://rextester.com/MCDGR84399
but how do I make the trigger scroll through all the Fkeys that t is linked to instead of just one
update = 2
my basic question is there a prettier way to do this :
CREATE OR REPLACE FUNCTION delete_cascade() RETURNS trigger AS $$BEGIN delete from u where u.uid = OLD.uid; delete from c where c.cid = OLD.cid; delete from b where b.bid = OLD.bid; delete from a where a.aid = OLD.aid; return OLD; END;$$ LANGUAGE plpgsql; CREATE TRIGGER t_cascade AFTER DELETE ON t FOR EACH ROW EXECUTE PROCEDURE delete_cascade();
example code http://rextester.com/UAZU30980