samedi 9 mai 2015

SQL Trigger: Inserting a row in a table after an insert on another table, if a given value doesn't already exist in the first table

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