create trigger on update to insert old values into other table in MYSQL

Question

I have two tables. The first is my person table which has id, name, creation_date as values, I have a old_person table (id, name, modified_date) which I want to populate the value of person before it actually changes. How would I go about that? I have tried triggers but failed.

I tried as follows

create trigger Person_Trigger Update on person
before update
as
insert into old_person(id, name, modified) 
select id, new.name, getdate()
from person

It's giving me syntax errors...Not many Trigger references out there either, a little push would be greatly appreciated!

Answer 1

Have a look at the following example

SQL Fiddle DEMO

IO hade a bit of trouble myself, but from How to Create Triggers in MySQL there was a line that made me think

The first MySQL command we’ll issue is a little unusual:

DELIMITER $$

Our trigger body requires a number of SQL commands separated by a semi-colon (;). To create the full trigger code we must change delimiter to something else — such as $$.

Finally, we set the delimiter back to a semi-colon:

DELIMITER ;

So in the SQL Fiddle I changed the query terminator to GO and that seemed top work.

CREATE TABLE person
(
  id INT,
  name varchar(20)
)
GO
CREATE TABLE old_person
(
  id INT,
  name varchar(20),
  modified DATETIME
)
GO
CREATE TRIGGER Person_Trigger before update on person
FOR EACH ROW BEGIN
  INSERT INTO old_person(id, name, modified) 
  VALUES (OLD.id, OLD.name, NOW());
END
GO



INSERT INTO person VALUES (1,'TADA')
GO

UPDATE person SET name = 'FOO'
GO