Understanding `WHERE x = NEW.x` Syntax

Question

I'm writing an application that uses SQLite to store court information obtained from a government database in a client table. I've set up a DB Fiddle here, which is a simplification of my application.

• The first time a request is made to the database, the clients table 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 client_update table

• Upon insertion on the client_update table, I will run a trigger that will update the client table if any of the columns of client_update have new information. If the client table is updated, the original row from the client table should be logged in a log table

This question 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_update as an example:

1. A new row, (213310109, 'Janice', True) is inserted on the client_update table triggering tr_client_update. The actions of this trigger happen after the insertion of the row, and before (213310110, 'Jane', True) is inserted.

2. The INSERT statement in tr_client_update compares the row (213310109, 'Janice', True) just inserted into client_update against (213310109, 'John', True) in client because they have the same a_number. Since the name has changed it logs the row from client in client_log.

3. Using the same logic, the update statement updates the client table 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_number is the same in both tables, but either name or docketed was changed.

Answer 1

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_number is unchanged and name is changed (or not unchanged) OR
• a_number is unchanged and docketed is 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 x, y and z we get

(a_number = NEW.a_number) AND ((name != NEW.name) OR (docketed != NEW.docketed))

The operators = and != are equivalent to IS and IS NOT except that the latter should be used when one of the operands may be NULL.

I hope this helps.

Edit to add: Part of the OP's confusion was over the NEW element. NEW is a pseudorecord available to ON INSERT and ON UPDATE triggers which represents the record which fired the trigger, as it will appear in the table once the firing transaction is committed.