我有两张桌子.第一个是我的person表,它有id,name,creation_date作为值,我有一个old_person表(id,name,modified_date),我想在实际更改之前填充person的值.我该怎么办呢?我尝试过触发但失败了.
我尝试如下
create trigger Person_Trigger Update on person
before update
as
insert into old_person(id, name, modified)
select id, new.name, getdate()
from person
它给了我语法错误…也没有很多触发器引用,一点推动将非常感激!
解决方法:
看看下面的例子
我自己也有点麻烦,但从How to Create Triggers in MySQL开始就有一条线让我思考
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 ;
因此,在SQL Fiddle中,我将查询终止符更改为GO,这似乎是最重要的工作.
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