Why this Trigger has syntax error ? MySQL Workbench ERROR:1064

for a project having lists of products, i'm trying to make some statistics but the Tigrer that i'm creating seams to have syntax errors on compilation level (no error before execute).

In my table stats there a coulmn day type:DATE and the others are INT DEFAULT 0, my trigger should increment the stats table when a list is created.

To ensure a row with the DATE exist, i have a Trigger before the UPDATE() and a procedure to distinct everything. But none of them actually work with or without delimiter and in different ways to write the body.


DROP PROCEDURE IF EXISTS new_day;
Delimiter $
CREATE PROCEDURE new_day()
BEGIN
-- seems that the error come from the WHERE day = DATE( "here" )) is...
    IF (SELECT day FROM stats WHERE day = DATE()) is null THEN
        INSERT INTO stats (day) VALUES (DATE());
    END IF;
END;
END$ 
Delimiter ;


DROP TRIGGER IF EXISTS Trig_BEFORE_ANY_update_stats; 
Delimiter $
CREATE TRIGGER Trig_BEFORE_ANY_update_stats
BEFORE UPDATE ON stats FOR EACH ROW 
BEGIN
call new_day() ;
IF (SELECT day from stats where day=DATE()) is null THEN
        SIGNAL SQLSTATE VALUE '45000'
            SET MESSAGE_TEXT = "There is no tomorrow, die()";
END IF; 
END$ 
Delimiter ;


DROP TRIGGER IF EXISTS Trig_AFTER_LIST_CREATED;
Delimiter $
CREATE TRIGGER Trig_AFTER_LIST_CREATED
AFTER INSERT ON SAE_Liste FOR EACH ROW
BEGIN
UPDATE stats SET nb_List_CREATED_per_Day = nb_List_CREATED_per_Day + 1 WHERE day = DATE();
END$
Delimiter ;

I excepted that everything worked but it didn't and instead i got :

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) is null THEN         INSERT INTO stats (day) VALUES (DATE(NOW()));     END...' at line 3

Solution

Thanks to people comments, the problem was no more. The table cannot be what call the trigger and then the trigger call the same table.

After using the proposed IODKU and variant everywere it worked.

Thanks everyone.

CREATE TRIGGER Trig_AFTER_LIST_DELETE
AFTER DELETE ON SAE_Liste FOR EACH ROW 
BEGIN
    INSERT INTO stats (`day`, `nb_List_DELETE_per_Day`) VALUES (CURRENT_DATE, 1)
    ON DUPLICATE KEY UPDATE `nb_List_DELETE_per_Day` = `nb_List_DELETE_per_Day` + 1;
END $ DELIMITER ;