An Correction! /***************************************************************************/ DROP TRIGGER IF EXISTS movement_trg02; CREATE TRIGGER movement_trg02 AFTER INSERT ON movement FOR EACH ROW BEGIN REPLACE INTO movement SELECT t1.id , t1.account_id , t1.payment , t1.amount , (COALESCE((SELECT balance FROM movement s1 WHERE s1.account_id = t1.account_id AND (s1.payment < t1.payment OR (s1.payment = t1.payment AND s1.id < t1.id)) GROUP BY s1.account_id HAVING s1.payment = MAX(s1.payment) AND s1.id = MAX(s1.id)), 0) + t1.amount) AS balance FROM movement t1 WHERE id = new.id;
UPDATE movement SET balance = balance + new.amount WHERE account_id = new.account_id AND (payment > new.payment OR (payment = new.payment AND id > new.id)); END; -- account_id or payment changed DROP TRIGGER IF EXISTS movement_trg03; CREATE TRIGGER movement_trg03 AFTER UPDATE ON movement FOR EACH ROW WHEN (((old.account_id != new.account_id) OR (old.payment != new.payment)) AND (new.amount != old.amount)) BEGIN -- same code as delete trigger UPDATE movement SET balance = balance - old.amount WHERE account_id = old.account_id AND (payment > old.payment OR (payment = old.payment AND id > old.id)); -- same code as insert REPLACE INTO movement SELECT t1.id , t1.account_id , t1.payment , t1.amount , (COALESCE((SELECT balance FROM movement s1 WHERE s1.account_id = t1.account_id AND (s1.payment < t1.payment OR (s1.payment = t1.payment AND s1.id < t1.id)) GROUP BY s1.account_id HAVING s1.payment = MAX(s1.payment) AND s1.id = MAX(s1.id)), 0) + t1.amount) AS balance FROM movement t1 WHERE id = new.id; UPDATE movement SET balance = balance + new.amount WHERE account_id = new.account_id AND (payment > new.payment OR (payment = new.payment AND id > new.id));; END; /***************************************************************************/ -- Regards/Atenciosamente, Israel Lins Albuquerque Developer/esenvolvimento Polibrás Brasil Software Ltda. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
