On Wed, Oct 19, 2011 at 6:11 PM, Chris Travers <[email protected]> wrote: > Just in case anyone is interested, here's some SQL code that, assuming > no major db changes since last time I looked, should prevent this > problem. > > As a note, I haven't tested the delete portions of the trigger against > the SQL-Ledger code. You may have to remove the delete portions of > the trigger to get them to work smoothly. YMMV. > > Best Wishes, > Chris Travers > > --BEGIN SQL CODE HERE > > > CREATE TABLE transactions ( > id int PRIMARY KEY, > table_name text, > ); > > > CREATE OR REPLACE FUNCTION track_global_sequence() RETURNS TRIGGER AS > $$ > BEGIN > IF tg_op = 'INSERT' THEN > INSERT INTO transactions (id, table_name) > VALUES (new.id, TG_RELNAME); > ELSEIF tg_op = 'UPDATE' THEN > IF new.id = old.id THEN > return new; > ELSE > UPDATE transactions SET id = new.id WHERE id = old.id; > END IF; > ELSE > DELETE FROM transactions WHERE id = old.id; > END IF; > RETURN new; > END; > $$ LANGUAGE PLPGSQL; > > > COMMENT ON FUNCTION track_global_sequence() is > $$ This trigger is used to track the id sequence entries across the > transactions table, and with the ar, ap, and gl tables. This is necessary > because these have not been properly refactored yet. > $$; > > > FOR EACH ROW EXECUTE PROCEDURE track_global_sequence(); > > CREATE TRIGGER ar_track_global_sequence BEFORE INSERT OR UPDATE OR DELETE ON > ar > FOR EACH ROW EXECUTE PROCEDURE track_global_sequence(); > CREATE TRIGGER ap_track_global_sequence BEFORE INSERT OR UPDATE OR DELETE ON > ap > FOR EACH ROW EXECUTE PROCEDURE track_global_sequence(); > CREATE TRIGGER gl_track_global_sequence BEFORE INSERT OR UPDATE OR DELETE ON > gl > FOR EACH ROW EXECUTE PROCEDURE track_global_sequence(); > > ALTER TABLE ar ADD FOREIGN KEY (id) REFERENCES transactions(id); > ALTER TABLE ap ADD FOREIGN KEY (id) REFERENCES transactions(id), > ALTER TABLE gl ADD FOREIGN KEY (id) REFERENCES transactions(id); > ALTER TABLE acc_trans ADD FOREIGN KEY (trans_id) REFERENCES transactions(id);
Chris, Thanks a lot. This code is surely going to help during future enhancements. Regards Armaghan _______________________________________________ SQL-Ledger mailing list [email protected] http://lists.ledger123.com/mailman/listinfo/sql-ledger
