Haskell Sqlite 3 CASE statement syntax error

问题:

This is HDBC and Sqlite3. It says there's a syntax error near "CASE". But I can'd find such. Is there any ?

calculateNoOfStocksTraded::String->Database.HDBC.Sqlite3.Connection->IO () calculateNoOfStocksTraded code conn=do

                                run conn " CREATE TRIGGER calcStocks \
                                           \ AFTER INSERT ON historicalData \
                                           \ FOR EACH ROW \
                                           \ BEGIN \
                                           \ CASE WHEN (SELECT COUNT(*) FROM historicalData) >= 1  THEN \
                                           \    UPDATE company \
                                           \    SET noOfStocks=(SELECT SUM(volume) FROM historicalData  WHERE companyCode= ? ) \
                                           \    WHERE code= ? ; \
                                           \ ELSE \
                                           \    UPDATE company \
                                           \    SET noOfStocks=0  \
                                           \    WHERE code= ? ; \
                                           \ END \

                                           \ END; " [toSql code,toSql code, toSql code]

                                commit conn

回答1:

CREATE TRIGGER expects as the body of the trigger to be one of update-stmt, insert-stmt, delete-stmt or select-stmt; So we need to rewrite the trigger in terms of one of those things;

As it turns out, you can simplify your trigger such that we can replace the CASE syntax with nothing; if count(foo.*) < 1, then it must be the case that sum(foo.bar) = 0 and thus:

In sqlite; count(foo.*) < 1, causes sum(foo.bar) IS NULL which is unfortunate; but we can make it do the right thing by turning the null into a 0 with coalesce():

CREATE TRIGGER calcStocks
AFTER INSERT ON historicalData
FOR EACH ROW
BEGIN
   UPDATE company
   SET noOfStocks=(SELECT COALESCE(SUM(volume), 0) FROM historicalData  WHERE companyCode= ? )
   WHERE code= ? ;
END