What is my mistake when creating SQlite trigger?

Question

SQLite

Hello!

There is an accounts table with columns Active (Bool), СheckLimit (Bool), MaxLimit (Numeric), CheckCount (Numeric), LimitReached(Bool). Task: set True in the LimitReached column of the row in which the field Active is True, CheckLimit is True, CheckCount == MaxLimit after the LimitCount column update event.

I give the lines below, according to the condition, two lines (3 and 4) are suitable, when the LimitCount field is updated to 5 and 6, respectively.


I tried to create a trigger like this, but it doesn't work:

sqlite code

CREATE TRIGGER SetCheckLimit
         AFTER UPDATE OF LimitCount
            ON accounts
      FOR EACH ROW
          WHEN ( ( (
                     SELECT Active
                       FROM accounts
                 )
                 IS 1) AND 
                 ( (
                     SELECT CheckLimit
                       FROM accounts
                 )
                 IS 1) AND 
                 ( (
                       SELECT CheckCount
                         FROM accounts
                   )
==               (
                     SELECT MaxLimit
                       FROM accounts
                 )
                 ) ) 
BEGIN
    UPDATE accounts
       SET LimitReached = 1;
END;

I tried to start with one condition, for example Active is True - it doesn't work either.
Judging by the syntax, you can write rather complex conditions for a trigger, so I made a mistake somewhere in the expression. Where did I go wrong?

Syntax of sqlite expressions

Answer 1

What are the hellish selects in WHEN? You have the NEW and OLD lines, so write the conditions:

WHEN (NEW.Active = 1 AND NEW.CheckLimit = 1 AND NEW.CheckCount = NEW.MaxLimit)