Error when creating SQL trigger to update another table

Question

I get the syntax error at or near "ON" with the code below. What is the correct way to write this? I'm trying to create a trigger for the game_info table that will update the game_count table when data is added to game_info.

CREATE TRIGGER my_trigger
    ON game_info
    AFTER INSERT
AS
BEGIN
    INSERT INTO game_count(title,count)
    SELECT title, count(*)
    FROM public.game_info
    GROUP BY title
    ORDER BY count DESC
END

creating the game_info table

CREATE TABLE game_info (
rental_id INT NOT NULL,
film_id SMALLINT NOT NULL,
title CHAR(255) NOT NULL,
description TEXT NOT NULL,
length SMALLINT NOT NULL
);

creating the game_count table

CREATE TABLE game_count (
title CHAR(255) NOT NULL,
count BIGINT NOT NULL
)

inserting data into game_info

INSERT INTO game_info (rental_id, film_id, title, description, length)
SELECT
r.rental_id,
i.film_id,
f.title,
f.description,
f.length
FROM public.inventory as i
INNER JOIN public.rental as r ON r.inventory_id=i.inventory_id
INNER JOIN public.film as f ON f.film_id=i.film_id

inserting data into game_count

INSERT INTO game_count(title, count)
SELECT title, count(*)
FROM public.game_info
GROUP BY title
ORDER BY count DESC;

Move after insert above on game_info and give it a shot again — zedfoxus
– zedfoxus, Commented Sep 21, 2021 at 3:27
@zedfoxus It gives me the error message "syntax error at or near "AS" — ChickenRex
– ChickenRex, Commented Sep 21, 2021 at 3:54
remove as and try again. Also, follow these tutorials: mysqltutorial.org/create-the-first-trigger-in-mysql.aspx and w3resource.com/mysql/mysql-triggers.php#MTAI — zedfoxus
– zedfoxus, Commented Sep 21, 2021 at 3:58
Your trigger logic seems to be wrong. In current state (after syntax fixing) it will insert a bunch of new rows into the destination table for each inserted row... maybe you need to renew the data in existing rows instead (and insert new row when according game row not exists)? — Akina
– Akina, Commented Sep 21, 2021 at 6:23

Ergest Basha · Accepted Answer · 2021-09-21 08:57:32Z

2

You can try:

DELIMITER $$
CREATE TRIGGER my_trigger AFTER INSERT ON game_info
FOR EACH ROW
BEGIN
    INSERT INTO game_count set title=new.title, count=(select count(*) from FROM game_info GROUP BY title) ;    
END$$
DELIMITER ;

The trigger works even for multiple insert values on game_info table.

edited Sep 21, 2021 at 8:57

answered Sep 21, 2021 at 8:41

Ergest Basha

9,3164 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error when creating SQL trigger to update another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related