MySQL 5 After Update Trigger Not Working

Question

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);

It is not inserting/updating record.

Maximus
– Maximus

2011-10-09 21:47:41 +00:00
Commented Oct 9, 2011 at 21:47 — Maximus
– Maximus, Commented Oct 9, 2011 at 21:47

Russell Hart · Accepted Answer · 2011-10-09 22:17:04Z

1

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.

answered Oct 9, 2011 at 22:17

Russell Hart

1,85113 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Russell Hart Over a year ago

You can test it with a simple select. SELECT 'Trigger Invoked';

Marc Alff · Accepted Answer · 2012-01-19 08:33:40Z

0

The trigger is an 'AFTER UPDATE' trigger ... It won't be executed after an INSERT statement.

What you are looking for is more likely to use an 'AFTER INSERT' trigger on table test.

answered Jan 19, 2012 at 8:33

Marc Alff

8,43536 silver badges62 bronze badges

Collectives™ on Stack Overflow

MySQL 5 After Update Trigger Not Working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related