Create trigger in MySQL to log changes in database

Question

UPDATE: the delimiter thing actually solved my original problem. But I now ended up with a new Error. I'll describe it below along with the implemented changes that solved the first problem.

I am trying to write a trigger that will log changes in the database to a separate table. But I keep getting the same error. I have looked at the MySQL documentation and searched this forum and found a lot of helpful answers. Trouble is that I now have a piece of SQL code that looks exactly like the answers given in this forum but I still get an error.

The trigger I am trying to use is:

CREATE TRIGGER logInsert AFTER INSERT ON test_table
    FOR EACH ROW
    BEGIN
      INSERT INTO datalog (action, id, timestamp, data1, data2)
      VALUES ('insert', NEW.id, NOW(), NEW.data1, NEW.data2);
    END;

The error message I get back is:

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 '' at line 5

I have tried using " instead of single quotes. someone suggested in on of the answers here that you should use backticks, so tried that as well.

No matter what, the error message is exactly the same.

A friendly soul here posted a fix for my original error. I needed to add DELIMITER to the statement so that it should look like this:

DELIMITER $$
CREATE TRIGGER logInsert AFTER INSERT ON test_table
    FOR EACH ROW
    BEGIN
      INSERT INTO datalog (action, id, timestamp, data1, data2)
      VALUES ('insert', NEW.id, NOW(), NEW.data1, NEW.data2);
    END$$
DELIMITER ;

This change solved the original error, but also let to a new error.

The new error is:

Unknown Column 'id' in table NEW

This is the table I'm trying to write to:

CREATE TABLE datalog (
      id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      timestamp   TIMESTAMP,
      data1 VARCHAR(255) NOT NULL,
      data2  DECIMAL(5,2) NOT NULL 
);

Hope someone here has an idea of what is wrong.

Kind regards, Jonas

If your trigger/function/procedure has multiple statements, or uses BEGIN END; you need to override DELIMITER so the parser does not assume you are trying to end the definition the first time it hits a semicolon. Typically, DELIMITER $$ followed by def terminated with END$$, followed by DELIMITER ; to return things to normal. — Uueerdo
– Uueerdo, Commented Jan 28, 2020 at 17:08
Thanks. that solved part of the problem, now I have run into a new error. — Jonas
– Jonas, Commented Jan 28, 2020 at 17:44

nbk · Accepted Answer · 2020-01-28 17:09:23Z

1

Use DELIMITER for the trigger Like

DELIMITER $$
CREATE TRIGGER logInsert AFTER INSERT ON test_table
FOR EACH ROW
BEGIN
  INSERT INTO datalog (action, id, timestamp, data1, data2)
  VALUES ('insert', NEW.id, NOW(), NEW.data1, NEW.data2);
END$$
DELIMITER ;

answered Jan 28, 2020 at 17:09

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

2 Comments

Jonas Over a year ago

Great, thanks. That is actually the first time I have gotten a different error. Now I get the error "unknown colunm 'id' i tabel NEW

nbk Over a year ago

Then your test_table has no id Column

Collectives™ on Stack Overflow

Create trigger in MySQL to log changes in database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related