Skip to content
Advertisement

MYSQL Trigger not adding count after inserting rows into another table

This is a continuation of this question: Insert data into a table with a foreign key SQL

I need to create a trigger that updates a counter variable after I insert a row into a table. The counter variable keeps track of how many new rows are inserted into the ItemBook table. However, after running the script, the trigger doesn’t seem to be working. Also how can I reuse this trigger if I have created it already?

CREATE TABLE count (
        countBook INT DEFAULT 0 NOT NULL,
);



CREATE 
    TRIGGER count_trigger AFTER INSERT
    ON ItemBook
    FOR EACH ROW
        UPDATE count
        SET countBook = (SELECT COUNT(*) FROM Itembook)

INSERT INTO Item
VALUES('Clippers','amazon.com', 'hair clippers');


SET SQL_SAFE_UPDATES = 0;
INSERT INTO ItemBook
VALUES('Clippers','Bob')  
SET SQL_SAFE_UPDATES = 1;

SELECT * FROM count;

The countBook column returns nothing after running the script. Furthermore, if I try to rerun this script again it will say Trigger already exists (after creating it). How do I reuse this trigger?

Advertisement

Answer

The trigger would look something like this:

CREATE TRIGGER count_trigger AFTER INSERT ON ItemBook
FOR EACH ROW
BEGIN
    UPDATE count
        SET countBook = countBook + 1;
END;

It seems very curious to have a table with one row for this information. But this appear to be what you are trying to do.

6 People found this is helpful
Advertisement