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.