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 client table. 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 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 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_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.



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2