84669 person learning
152542 person learning
20005 person learning
5487 person learning
7821 person learning
359900 person learning
3350 person learning
180660 person learning
48569 person learning
18603 person learning
40936 person learning
1549 person learning
1183 person learning
32909 person learning
mysql 的触发器的问题,我想写一个触发器,用来监控table1的price字段,或者监控table1的多个字段,当这些个字段发生改变的时候,在table_log1总记录该条记录的改变之前的值与改变之后的值,就是一个改变日志!求方法
光阴似箭催人老,日月如移越少年。
-- 1建表CREATE TABLE test_update_trigger (id int(10) NOT NULL AUTO_INCREMENT,age varchar(10) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '测试触发器';
test_update_trigger
id
age
CREATE TABLE test_log (id int(10) NOT NULL AUTO_INCREMENT,
test_log
`update_id` int(10) not null default 0 comment '修改id',
log_text varchar(100) DEFAULT NULL, PRIMARY KEY (id),
log_text
key(update_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '日志';
-- 2插入数据insert into test_update_trigger values(1,10),(2,20);
-- 3设置触发器
create trigger tri_test_update after update on test_update_triggerfor each row begin insert into test_log(update_id,log_text) values(NEW.id,concat(cast(NEW.age as char),'修改为:',cast(OLD.age as char)));end
-- 4修改update test_update_trigger set age=30 where id=2;
-- 5查询结果select * from test_log;
id update_id log_text1 2 30修改为:20
-- 1建表
CREATE TABLE
test_update_trigger
(id
int(10) NOT NULL AUTO_INCREMENT,age
varchar(10) DEFAULT NULL,PRIMARY KEY (
id
)) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '测试触发器';
CREATE TABLE
test_log
(id
int(10) NOT NULL AUTO_INCREMENT,log_text
varchar(100) DEFAULT NULL,PRIMARY KEY (
id
),) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '日志';
-- 2插入数据
insert into test_update_trigger values(1,10),(2,20);
-- 3设置触发器
create trigger tri_test_update
after update on test_update_trigger
for each row
begin
insert into test_log(update_id,log_text) values(NEW.id,concat(cast(NEW.age as char),'修改为:',cast(OLD.age as char)));
end
-- 4修改
update test_update_trigger set age=30 where id=2;
-- 5查询结果
select * from test_log;
id update_id log_text
1 2 30修改为:20