MySQL 5 更新后触发器不起作用

发布于 2024-12-08 19:54:26 字数 976 浏览 0 评论 0原文

知道如何让 test_trigger 工作吗？

Create table test (
    book_id Int UNSIGNED NOT NULL,
    book_views Int UNSIGNED NOT NULL DEFAULT 0,
Primary Key (book_id)) ENGINE = InnoDB;

Create table trigger_test (
    book_id Int UNSIGNED NOT NULL,
    book_views Int UNSIGNED NOT NULL DEFAULT 0,
Primary Key (book_id)) ENGINE = Memory;

delimiter $$
CREATE TRIGGER test_trigger
   AFTER UPDATE ON test
   FOR EACH ROW
   BEGIN
     DECLARE rows_count INT;
     SELECT count(1) FROM trigger_test WHERE book_id=NEW.book_id INTO @rows_count;

     IF @rows_count = 0 THEN
         INSERT INTO trigger_test(book_id, book_views)
         SELECT book_id, book_views FROM test where book_id = NEW.book_id;
     ELSE
         UPDATE trigger_test
         SET book_views = NEW.book_views
         WHERE book_id = NEW.book_id;
     END IF;
   END$$
delimiter ;

插入/更新不起作用。以下代码应该在trigger_test中插入行，但它没有插入行。

insert into test values (1, 10);

原文

Any idea how to get test_trigger working?

Create table test (
    book_id Int UNSIGNED NOT NULL,
    book_views Int UNSIGNED NOT NULL DEFAULT 0,
Primary Key (book_id)) ENGINE = InnoDB;

Create table trigger_test (
    book_id Int UNSIGNED NOT NULL,
    book_views Int UNSIGNED NOT NULL DEFAULT 0,
Primary Key (book_id)) ENGINE = Memory;

delimiter $
CREATE TRIGGER test_trigger
   AFTER UPDATE ON test
   FOR EACH ROW
   BEGIN
     DECLARE rows_count INT;
     SELECT count(1) FROM trigger_test WHERE book_id=NEW.book_id INTO @rows_count;

     IF @rows_count = 0 THEN
         INSERT INTO trigger_test(book_id, book_views)
         SELECT book_id, book_views FROM test where book_id = NEW.book_id;
     ELSE
         UPDATE trigger_test
         SET book_views = NEW.book_views
         WHERE book_id = NEW.book_id;
     END IF;
   END$
delimiter ;

Insert/update is not working. Following code should insert row in trigger_test but its not inserting row.

insert into test values (1, 10);

分享到QQ

分享到微博

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

发布评论

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

苹果你个爱泡泡 2024-12-15 19:54:26

无需引用表“测试”。只需使用这样的 NEW 表

INSERT INTO trigger_test(book_id,book_views)
VALUES (NEW.book_id,NEW.book_views);

也可以使用 Replace 语句。

MySQL 替换语法

REPLACE 的工作方式与 INSERT 完全相同，只不过如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前会删除旧行。

There is no need to reference the table 'test'. Just use the NEW table like this

INSERT INTO trigger_test(book_id,book_views)
VALUES (NEW.book_id,NEW.book_views);

Also you could use the Replace statement.

MySQL Replace Syntax

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

回复收藏 0 原文