Why does this trigger not prevent rows from being inserted in sqlite?

user_13834264 0
Viewed 151

Here are my tables:

CREATE TABLE users(
    user_id INT,
    channel_id INT NOT NULL UNIQUE,
    PRIMARY KEY (user_id)
);

CREATE TABLE credits(
    user_id INT,
    number_of_items INT CHECK(number_of_items > 0),
    expiration DATETIME,
    PRIMARY KEY (user_id, number_of_items, expiration),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
);

CREATE TABLE users_items(
    user_id INT,
    item_id INT,
    PRIMARY KEY (user_id, item_id),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
);

and here is the trigger I have implemented:

CREATE TRIGGER check_has_enough_credits_to_monitor_item
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >
            (SELECT sum(number_of_items) FROM credits WHERE user_id = NEW.user_id)
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;

I want this trigger to prevent a row from being inserted in the users_items table if the user with that ID already has surpassed his credit limit or his credits have expired, however, when I have tested it using this fiddle the trigger does not raise an error. Currently the trigger just checks if the number of credits allowed against the number of items added to users_items and does not check the date, but I plan on checking the date by adding something like AND expiration >= datetime('now') to the trigger to make it look like this:

CREATE TRIGGER check_has_enough_credits_to_monitor_item
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >=
            (SELECT sum(number_of_items) FROM credits WHERE user_id = NEW.user_id AND expiration >= datetime('now'))
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;
count triggers sum sqlite
2 Answers

From long experience, I want to warn you, don't do this. Don't use triggers if you can use Declared Referential Integrity (DRI) instead, and don't use triggers for anything other than referential integrity. In particular, don't use triggers for business rules.

If you don't follow that policy, one day you'll wish you had.

Instead, use a transaction. For example, write your insert statement to enforce the rule:

insert into user_items
select ( ... values ... )
where [user has not surpassed his credit limit and his credits have not expired]

Test the rows affected to see if it's zero or one, and display an error if nothing was inserted (meaning the criteria were not satisfied).

In my SQLite applications, I keep an associative array of named "stored procedures", so the SQL never appears in the application logic. I look up the SQL text by its name in the array, prepare it, and execute it. That way, if there's a problem with the SQL, there's only one place to look.

As an example of what can go wrong if you use triggers for business rules, consider what happens if two users are inserted at the same time, one that meets the criteria and another that does not. Do you really want both rows rejected? (Because that's what will happen.) That's not a use case? Well, it is: you're writing a rule about the table, not what the application will do if the rule is violated. Tables can be updated outside applications.

Think of it this way: if your rule is violated, your data are still correct. Nothing is inconsistent within the database. All that's happened is a user has been allowed to exceed some constraint. You can flag that anytime: a period sweep of the table, a check before play begins, whatever. IOW, if the table somehow is updated by means other than your carefully written INSERT statement, you can always catch the error later, and set things aright.

user_4516010

You must use COALESCE() for the aggregate function SUM() so that when it returns NULL because there are no rows satisfying the conditions you get 0 instead and this is comparable to the result of COUNT(*) of the 1st subquery:

CREATE TRIGGER check_has_enough_credits_to_monitor_item
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >=
            (SELECT COALESCE(SUM(number_of_items), 0) FROM credits WHERE user_id = NEW.user_id AND expiration >= datetime('now'))
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;

See the demo.

user_104988280
Related