How can i create a trigger to verify if a record exists on another table?

Question

I am using version 5.0 of mysql.

I'm trying to create a trigger to check if one entry(name of Food) exists in the other table.

I´ve done this:

delimiter //
CREATE TRIGGER verifyExists BEFORE INSERT ON Sold
    FOR EACH ROW
    BEGIN
        IF NEW.nameF not in (
            select A.nameF
            From Available D
            where (NEW.nameF = A.nameF and NEW.nameR = A.nameR)
        )
        END IF;
    END;
//
delimiter ; 

this doesen't work, why?

Answer 1

You have a couple of errors:

delimiter //
CREATE TRIGGER verifyExists BEFORE INSERT ON Sold
    FOR EACH ROW
    BEGIN
        IF NEW.nameF not in (
            select A.nameF
            From Available A  -- CHANGED THE ALIAS TO A
            where (NEW.nameF = A.nameF and NEW.nameR = A.nameR)
        ) THEN -- MISSING THEN
           CALL `Insert not allowed`;

        END IF;
    END;
//
delimiter ; 

sqlfiddle demo

If you could use SIGNAL, it is the best way, but since it was only introduced in mysql 5.5, you will have to do it by other route. One way is to call a non existant function, like showed above. From this answer