Hello everyone,
Given a master table, a table of transactions and a table of cumulative
amounts per transaction master:
CREATE TABLE Customer (
id INTEGER PRIMARY KEY,
firstname VARCHAR(50),
lastname VARCHAR(50)
);
CREATE TABLE CustomerTrans (
id INTEGER PRIMARY KEY,
transactiondate DATE,
customerid INTEGER,
amount FLOAT
FOREIGN KEY (customerid) REFERENCES Customer (id)
);
CREATE TABLE CustomerAccum (
customerid INTEGER PRIMARY KEY,
amount FLOAT
FOREIGN KEY (customerid) REFERENCES Customer (id)
);
I would like to create a trigger on table CustomerTrans that performs the
accumulation of transaction amounts in CustomerAccum. This trigger should
- Insert a record with the transaction amount if a record for the
customerid does not exist
- Update an existing record, adding the transaction amount to the
cumulative amount.
Normally this would be done with a merge statement:
CREATE TRIGGER AI_CustomerTrans
AFER INSERT ON CustomerTrans FOR EACH ROW
BEGIN
MERGE INTO CustomerAccum
USING (SELECT new.customerid, new.amount) trans
ON trans.customerid = CustomerAccum.customerid
WHEN MATCHED THEN
UPDATE SET CustomerAccum.amount = CustomerAccum.amount + trans.amount
WHEN NOT MATCHED THEN
INSERT (customerid, amount) VALUES (trans.customerid, trans.amount);
END;
but since MERGE statements are not supported by SqLite, and trigger code
does not support flow control (IF THEN ELSE constructs) I think that the
only way to do this is by using the conflict clause and always try to
insert a new record:
CREATE TRIGGER AI_CustomerTrans
AFTER INSERT ON CustomerTrans FOR EACH ROW
BEGIN
INSERT OR IGNORE INTO CustomerAccum (customerid, amount)
VALUES (new.customerid, 0);
UPDATE CustomerAccum SET amount = amount + new.amount
WHERE CustomerAccum.customerid = new.customerid;
END;
But this will always perform two index scans on CustomerAccum, which is
suboptimal for the vast majority of trigger invokations.
Is there another way to achieve this MERGE without the double scan?
Mind you that the case I described is a simplified version of the problem I
am trying to solve, where the tables have much more dimensions than just
the customerid, and the size of data will be of the order of hundreds of
thousands of rows, thus my concern for the redundant most of the times
INSERT OR IGNORE.
Mind you also that I want to avoid the INSERT OR REPLACE construct because
on conflict it deletes and reinserts records invoking any delete triggers
and FK ON DELETE clauses which does not suit my needs.
Thanks in advance
--Constantine.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
