How to update a value to 0 if the result is null after a delete trigger in SQlite?-CodePudding

I have 2 Sqlite tables, lets call them main and sub.

Main has a field which should be the sum of all values with the same code in the sub table.

so if Main has

CODE  | TOTAL_UNITS_AVAILABLE 
1            3
2            5

Then it means in sub there's something like this

INGREDIENT_CODE  | UNITS_AVAILABLE
     1              1
     1              2
     2              5

I have triggers on sub for INSERT, UPDATE and DELETE which update the value of TOTAL_UNITS_AVAILABLE in Main for the record with the code that was inserted/updated/deleted on sub

Insert and update work fine, but DELETE has a problem where if every record in sub with an X main code gets deleted, the sum of amount in main gets updated to NULL.

I tried the following to prevent it but it's not working:

CREATE TRIGGER UpdateAmountAvailable_UPDATE
AFTER DELETE ON Sub
    BEGIN
        UPDATE Main
            SET 
                TOTAL_UNITS_AVAILABLE = (
                    SELECT
                        CASE SUM(A.UNITS_AVAILABLE)
                            WHEN NULL THEN 0
                            WHEN 0 THEN 0
                            ELSE SUM(A.UNITS_AVAILABLE)
                        END
                    FROM Sub AS A
                    WHERE A.INGREDIENT_CODE = old.INGREDIENT_CODE
                ) WHERE CODE = old.INGREDIENT_CODE;
    END

Is there any workaround I could try?

CodePudding user response：

Sqlite has a non-standard aggregate function TOTAL

The sum() and total() aggregate functions return sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0.

CREATE TRIGGER UpdateAmountAvailable_DELETE
AFTER DELETE ON Sub
BEGIN
  UPDATE Main
  SET TOTAL_UNITS_AVAILABLE = (
    SELECT TOTAL(A.UNITS_AVAILABLE) 
    FROM Sub AS A
    WHERE A.INGREDIENT_CODE = old.INGREDIENT_CODE
  )       
  WHERE CODE = old.INGREDIENT_CODE;
END

The same using the standard SUM function would be COALESCE(SUM(A.UNITS_AVAILABLE), 0)

db<>fiddle