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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x