At 10:26 04/07/2016, you wrote:
Hello,
I have a curious situation involving a trigger, which Iâm at a loss
to explain. Iâm wondering if someone who knows more about the
insides of SQLite can tell me more about why itâs happening. Iâm
running SQLite 3.8.7.
The trigger code is at the bottom of this email. Itâs a
straightforward AFTER UPDATE trigger, firing when any of three fields
is updated and when the old value is not the same as the new value for
any of those fields. Thereâs also a test Iâve put in there to make
sure that some tables which Iâm looking to update are not empty.
The table TriggerLog is a table Iâve added for testing. If the
trigger fires, a row is inserted into the log. I have tested that this
works when the trigger fires.
There are then a number of UPDATE statements to set flags on other
tables. In the circumstances Iâm running this in at the moment,
there are no rows in QuoteCalc, SaleCalc, TxnCalc etc., so the
triggerâs code wonât actually fire. I have verified this by
inspecting TriggerLog after the text; there are no rows reporting that
ContactCalcUpdate8 has fired.
During the test, a row on ContactCalc has one of its
ContactCalc_Phone, ContactCalc_Email or ContactCalc_Website fields
updated. This occurs on various rows of ContactCalc approximately
5,000 times. This stage of the test takes approximately 22 seconds.
If I remove all of the UPDATE rows from this trigger, and make no
other changes, the test takes approximately 12 seconds.
I donât understand why removing code from the body of a trigger
which doesnât fire makes things faster.
I have also tried replacing the WHERE clauses of all of the UPDATE
statements with WHERE 1=0 to eliminate the possibility of the IN
statement being the culprit; itâs still slow.
I would very much like to understand whatâs going on here; perhaps
someone can enlighten me.
Thanks,
Hamish
// When ContactCalc's phone, email or website changes, update the
phone, email and website fields in
// QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc.
// Also update GroupHead phone, email and web fields for QuoteCalc,
SaleCalc, PurchaseCalc, TxnCalc
CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE
OF ContactCalc_Phone,
ContactCalc_Email,
ContactCalc_Website
ON ContactCalc WHEN
Old.ContactCalc_Phone != New.ContactCalc_Phone
OR
Old.ContactCalc_Email != New.ContactCalc_Email
OR
Old.ContactCalc_Website != New.ContactCalc_Website
AND
(
SELECT COUNT(QuoteCalc_QuoteUUID) FROM QuoteCalc
) > 0
OR
(
SELECT COUNT(SaleCalc_SaleUUID) FROM SaleCalc
) > 0
OR
(
SELECT COUNT(PurchaseCalc_PurchaseUUID) FROM PurchaseCalc
) > 0
OR
(
SELECT COUNT(TxnCalc_TxnUUID) FROM TxnCalc
) > 0
BEGIN
INSERT INTO TriggerLog( TriggerLog_Name ) VALUES
('ContactCalcUpdate8');
UPDATE QuoteCalc
SET QuoteCalc_UpdateContactFlag = 1
WHERE QuoteCalc_ContactUUID = New.ContactCalc_ContactUUID;
UPDATE SaleCalc
SET SaleCalc_UpdateBillingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_BillingContactUUID = New.ContactCalc_ContactUUID
);
UPDATE SaleCalc
SET SaleCalc_UpdateShippingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_ShippingContactUUID = New.ContactCalc_ContactUUID
);
UPDATE TxnCalc
SET TxnCalc_UpdateContactFlag = 1
WHERE TxnCalc_TxnUUID IN
(
SELECT TxnCalc_TxnUUID
FROM TxnCalc
WHERE TxnCalc_ContactUUID = New.ContactCalc_ContactUUID
);
UPDATE PurchaseCalc
SET PurchaseCalc_UpdateContactFlag = 1
WHERE PurchaseCalc_PurchaseUUID IN
(
SELECT PurchaseCalc_PurchaseUUID
FROM PurchaseCalc
WHERE PurchaseCalc_ContactUUID = New.ContactCalc_ContactUUID
);
UPDATE ContactCalc
SET ContactCalc_UpdateGroupHeadFlag = 1
WHERE ContactCalc_ContactUUID IN
(
SELECT Contact_UUID
FROM Contact
WHERE Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);
UPDATE QuoteCalc
SET QuoteCalc_UpdateGroupHeadFlag = 1
WHERE QuoteCalc_QuoteUUID IN
(
SELECT Quote_UUID
FROM Quote,
Contact
WHERE Quote_ContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);
UPDATE SaleCalc
SET SaleCalc_UpdateBillingGroupHeadFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale,
Contact
WHERE Sale_BillingContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);
UPDATE SaleCalc
SET SaleCalc_UpdateShippingGroupHeadFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale,
Contact
WHERE Sale_ShippingContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);
UPDATE TxnCalc
SET TxnCalc_UpdateGroupHeadFlag = 1
WHERE TxnCalc_TxnUUID IN
(
SELECT Txn_UUID
FROM Txn,
Contact
WHERE Txn_ContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);
UPDATE PurchaseCalc
SET PurchaseCalc_UpdateGroupHeadFlag = 1
WHERE PurchaseCalc_PurchaseUUID IN
(
SELECT Purchase_UUID
FROM Purchase,
Contact
WHERE Purchase_ContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);
END;
It's known that triggers aren't SQLite most remarkable strength, even
if I never encountered a situation where their relative slowness
significantly exceeds their usefulness.
In your situation I believe your coumpond triggering condition needs
parenthesis. Your indentation of AND is meaningless to the parser. Try
this:
... WHEN
(
Old.ContactCalc_Phone != New.ContactCalc_Phone
OR
Old.ContactCalc_Email != New.ContactCalc_Email
OR
Old.ContactCalc_Website != New.ContactCalc_Website
)
AND
(
(SELECT COUNT(QuoteCalc_QuoteUUID) FROM QuoteCalc) > 0
OR
(SELECT COUNT(SaleCalc_SaleUUID) FROM SaleCalc) > 0
OR
(SELECT COUNT(PurchaseCalc_PurchaseUUID) FROM PurchaseCalc) > 0
OR
(SELECT COUNT(TxnCalc_TxnUUID) FROM TxnCalc) > 0
)
...
JcD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
