数据源
pubs 示例数据库
目标
创建 authors 表的日志表,记录修改city 列时的用户、新值、旧值、时间
代码
-- 创建修改日志
create table authors_log(
username varchar(50),
before_city varchar(20),
after_city varchar(20),
update_time datetime
)
-- 创建触发器
create trigger authors_update_trigger on authors
for update
as
declare @t_before_city varchar(20);
declare @t_after_city varchar(20);
begin
if(update(city))
begin
select @t_before_city = city from deleted;
select @t_after_city = city from inserted;
insert into authors_log values(user, @t_before_city, @t_after_city, getdate());
end
end
-- 测试触发器
update authors set city = 'changchun' where city = 'changchun'