Creating a MySQL trigger to verify data on another table

Question

I am trying to set up a MySQL trigger that does the following:

• When someone inserts data into databaseA.bills, it verifies if databaseB.bills already has that row, if it doesn't, then it does an additional insert into databaseB.bills.

Here is what I have:

CREATE TRIGGER ins_bills AFTER INSERT ON databaseA.bills
FOR EACH ROW
  BEGIN
    IF NOT EXISTS (SELECT 1 FROM databaseB.bills WHERE billNumber=NEW.billNumber) THEN
      INSERT INTO databaseB.bills (billNumber) VALUES (NEW.billNumber)
    END IF
  END;//
DELIMITER ;

The problem is, I can't create it through mysql console or phpMyAdmin. It returns syntax errors near END IF END, and I am sure it's a delimiter problem.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF END' at line 6

What am I doing wrong?

Answer 1

Assuming you are using // as your delimiter, you just need to use semi-colons at the end of the sql stmts inside the body of the trigger, and use // after the trigger END:

CREATE TRIGGER ins_bills AFTER INSERT ON databaseA.bills
FOR EACH ROW
  BEGIN
    IF NOT EXISTS (SELECT 1 FROM databaseB.bills WHERE billNumber=NEW.billNumber) THEN
      INSERT INTO databaseB.bills (billNumber) VALUES (NEW.billNumber);
    END IF;
  END //

Answer 2

If you have unique indexes you always can make an INSERT IGNORE

 CREATE TRIGGER ins_bills AFTER INSERT ON databaseA.bills
    FOR EACH ROW
      BEGIN
          INSERT IGNORE INTO databaseB.bills (billNumber) VALUES (NEW.billNumber);
      END //