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);
_______________________________________________
SQL-Ledger mailing list
[email protected]
http://lists.ledger123.com/mailman/listinfo/sql-ledger
