As the title says, I'm trying to insert a row in one table, triggered after an insertion on another table, but only if a given value doesn't already exist in the first table.
Example:
I have one table, countries, which stores countries and their id's:
Countries
id country
1 England
2 France
... ...
I have another table, events, which stores information about events as follows:
Events
id timestamp city country
1 13435636 London England
2 45635742 Paris France
... ... ... ...
What I want to do: create a trigger so that after an insertion on the events table, insert a new row in the countries table with the country that the event occurred in, but only if that country doesn't already exist in the countries table.
What I have tried:
CREATE TRIGGER Update
AFTER INSERT ON events FOR EACH ROW
INSERT INTO countries (country) VALUES (New.country)
WHERE NOT EXISTS (SELECT country FROM countries WHERE country = New.country)
CREATE TRIGGER Update
AFTER INSERT ON events FOR EACH ROW
IF NOT EXISTS (SELECT country FROM countries WHERE country = New.country)
BEGIN
INSERT INTO countries (country) VALUES (New.country)
END
CREATE TRIGGER Update
AFTER INSERT ON events FOR EACH ROW
IF NOT EXISTS (SELECT country FROM countries WHERE country = New.country)
INSERT INTO countries (country) VALUES (New.country)
Along with some other variations, and all I get are syntax errors (error #1064).
Aucun commentaire:
Enregistrer un commentaire