Understanding `WHERE x = NEW.x` Syntax
I'm writing an application that uses SQLite to store court information obtained from a government database in a
clienttable. I've set up a https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=13d8556c979807f2b4bb6d6bfd7e6636 here, which is a simplification of my application.
The first time a request is made to the database, the
clientstable will be populated with the information.
When the user wants to check if there are any updates, a new request will be made and the results will be inserted into a
Upon insertion on the
client_updatetable, I will run a trigger that will update the
clienttable if any of the columns of
client_updatehave new information. If the
clienttable is updated, the original row from the
clienttable should be logged in a
This https://stackoverflow.com/questions/67136895/update-and-log-only-changed-rows-with-sql-in-sqlite got me started, and I've replicated it in the context of my application. But I can't seem to find a good resource explaining how this actually works. I'll run through where I'm not understanding, taking the first insertion into
client_updateas an example:
A new row,
(213310109, 'Janice', True)is inserted on the
tr_client_update. The actions of this trigger happen after the insertion of the row, and before
(213310110, 'Jane', True)is inserted.
tr_client_updatecompares the row
(213310109, 'Janice', True)just inserted into
(213310109, 'John', True)in
clientbecause they have the same
a_number. Since the
namehas changed it logs the row from
Using the same logic, the
updatestatement updates the
clienttable with the new information.
I am hoping a kind DB Admin can help me understand the logical steps that are happening here. I don't feel like my explanation is adequate. Why is the right statement for achieving this
WHERE a_number = NEW.a_number AND (name IS NOT NEW.name OR docketed IS NOT NEW.docketed);and not something like
WHERE a_number IS NOT NEW.a_number AND (name IS NEW.name OR docketed IS NEW.docketed);. This would seem to make sense because what I want to check is that that the
a_numberis the same in both tables, but either
carriann last edited by
This is not actually a DBA question but rather a Boolean algebra question (which means it might be more at home on Stack Overflow.)
In short, you want the trigger to fire if EITHER:
a_numberis unchanged and
nameis changed (or not unchanged) OR
a_numberis unchanged and
docketedis not unchanged.
We can then apply distribution (
(x AND y) OR (x AND z) == x AND (y OR z)) to reduce the expression, as follows:
Your requirement, as I've restated it above, can be expressed as
(a_number = NEW.a_number AND name != NEW.name) OR (a_number = NEW.number AND docketed != NEW.docketed)
If we let
- x = (a_number = NEW.a_number)
- y = (name != NEW.name)
- z = (docketed != NEW.docketed)
we can rewrite it as
(x AND y) OR (x AND z)
which distributes to
x AND (y OR z)
and substituting our values of
(a_number = NEW.a_number) AND ((name != NEW.name) OR (docketed != NEW.docketed))
!=are equivalent to
IS NOTexcept that the latter should be used when one of the operands may be
I hope this helps.
Edit to add: Part of the OP's confusion was over the
NEWis a pseudorecord available to
ON UPDATEtriggers which represents the record which fired the trigger, as it will appear in the table once the firing transaction is committed.