MySQL Trigger IF statement using Select

Question

I'm trying to create a trigger on my table, so that when I insert data, it tries to take the data from one column (Notes) and update another table's (masterData) column notes (Additional) with the data. If it can't find it, I want the insert statement to be copied to a different table (failedtofind). My trigger is below, but it reports that there is a syntax error on my IF statement, and I can't quite get it working:

DELIMITER $$
TRIGGER `testdb`.`testTable`
AFTER INSERT ON `testdb`.`testTable`
FOR EACH ROW
BEGIN    
    IF ((SELECT PrimaryID FROM testdb.masterData WHERE PrimaryID = NEW.PrimaryID) > 0 )
        UPDATE masterData AS t
        SET t.Additional = NEW.Notes
        WHERE t.PrimaryID = NEW.PrimaryID;
    ELSE
        INSERT IGNORE INTO failedtofind SET (all the columns);
    END IF;

END$$

Also, is this the way I should be doing it? I'm a novice when it comes to databases, so, as always, if I'm doing something wrong, please feel free to correct me.

Solution

You are missing a THEN after your IF(...) it should be

 IF ((SELECT PrimaryID FROM testdb.masterData WHERE PrimaryID = NEW.PrimaryID) > 0 ) THEN
 ...

I am not sure what the > 0 is suppose to do - if no record with that PrimaryID is found the SELECT statement will evaluate to NULL (not 0). But it should still work as expected because NULL > 0 evaluates to NULL and it will insert a row in failedtofind.

Comments

[DavisTasar]

I'm using the '> 0' to determine if the PrimaryID exists in the masterData table, and if it does to update. If it doesn't (the NULL, o 0), Insert the failedtofind table.

[Vatev]

Well it should work with the > 0. You should also be careful about getting more than one row in a IF(SELECT ...) (should be OK in this case if PrimaryID is a PRIMARY KEY).

[DavisTasar]

Sounds good, thanks. PrimaryID is in fact the primary key, but thanks for the warning!