创建 MySQL 触发器来验证另一个表上的数据

发布于 2024-09-01 07:09:18 字数 736 浏览 2 评论 0原文

我正在尝试设置一个执行以下操作的MySQL触发器：

当有人将数据插入databaseA.bills时，它会验证databaseB.bills是否已经有该行，如果没有，那么它会额外插入databaseB.bills 。

这就是我所拥有的：

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 ;

问题是，我无法通过 mysql 控制台或 phpMyAdmin 创建它。它返回 END IF END 附近的语法错误，我确信这是一个分隔符问题。

#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

我做错了什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浪菊怪哟 2024-09-08 07:09:18

如果您有唯一索引，您始终可以创建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 //

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 //

回复收藏 0 原文

懷念過去 2024-09-08 07:09:18

假设您使用 // 作为分隔符，则只需在触发器主体内的 sql stmts 末尾使用分号，并在触发器 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 //

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 //

回复收藏 0 原文

~没有更多了~