对sql数据库中的表做updete时如何判断那些数据项背更新为不同的值了？

Question

create trigger tr_test on 表
for updated
select a.原来的值,b.现在的值 into 表B from 表 a,inserted b
on a.code=b.code

Answer 1

create trigger tr_test on 表
for updated
as
select a.原来的值,b.现在的值 into 表B from 表 a,inserted b
on a.code=b.code

Answer 2

-----原表(商品表)
create table scm_info(
scm_id varchar (20),
scm_name varchar (100),
scm_lsj decimal (12,2))
-----新建表（记录个修改、删除记录）
/*
history_scm_id 记录下更新、删除、插入操作所针对的scm_id;
history_scm_name 记录下该scm_id所对应的原scm_name;
history_scm_lsj 记录下该scm_id所对应的原scm_lsj;
hostname 记录下操作记录的计算机名;(这个没有测过)
username 记录下操作用户名；
update_date 记录下操作发后时间性；
action 记录下操作类型；*/
create table history_scm_info (
id int IDENTITY (1, 1) NOT NULL,
history_scm_id varchar (20),
history_scm_name varchar (100),
history_scm_lsj decimal (12,2),
hostname varchar (30),
username varchar (25),
update_date datetime,
action varchar (50),
)
----创建记录删除操作触发器
create trigger delete_history on scm_info
for delete 
as 
insert into history_scm_info (
history_scm_id,history_scm_name,history_scm_lsj,hostname,username,update_date,action)
select 
scm_id,scm_name,scm_lsj,
host_name() as hostname,SYSTEM_USER as username,GETDATE() as update_date,'删除操作' as action
 from deleted
----创建更新操作的触发器
create trigger update_history on scm_info
for update
as 
insert into history_scm_info (
history_scm_id,history_scm_name,history_scm_lsj,hostname,username,update_date,action)
select scm_id,scm_name,scm_lsj,
 host_name(),SYSTEM_USER as username,GETDATE() as update_date,'更新以前' as action
from deleted
insert into history_scm_info (
history_scm_id,history_scm_name,history_scm_lsj,hostname,username,update_date,action)
select scm_id,scm_name,scm_lsj,
 host_name(),SYSTEM_USER as username,GETDATE() as update_date,'更新以后' as action
from inserted
----创建插入操作的触发器create trigger insert_history on scm_info
for insert
as 
insert into history_scm_info (
history_scm_id,history_scm_name,history_scm_lsj,hostname,username,update_date,action)
select 
scm_id,scm_name,scm_lsj,
host_name(),SYSTEM_USER as username,GETDATE() as update_date,'插入操作' as action
from inserted------测试
insert into scm_infoselect 'SP000000008','善存片              ',85.5000 union
select 'SP000000009','施尔康片            ',67.5000 union
select 'SP000000011','盖天力片            ',28.8000 union
select 'SP000000012','小儿善存片          ',69.0000 union
select 'SP000000013','迪巧钙片            ',108.0000 union
select 'SP000000014','21金维他片          ',48.0000 union
select 'SP000000015','钙尔奇D(成人)       ',71.4000 union
select 'SP000000016','钙尔奇D600咀嚼片(成',135.0000 union
select 'SP000000017','钙尔奇(儿童）       ',90.0000 union
select 'SP000000018','钙尔奇D(儿童)       ',51.3000 union
select 'SP000000019','金施尔康片          ',74.4000 union
select 'SP000000020','小施尔康片          ',57.0000 union
select 'SP000000021','乐力钙              ',81.0000 union
select 'SP000000022','钙立得片            ',57.0000 union
select 'SP000000023','活性钙冲剂          ',11.4000 union
select 'SP000000024','葡萄糖酸锌片        ',8.4000 union
select 'SP000000025','小儿多维钙冲剂      ',48.6000 union
select 'SP000000026','葡萄糖酸钙片        ',6.0000 union
select 'SP000000028','清脑复神液          ',61.2000 union
select 'SP000000029','中汇川黄液          ',67.5000 union
select 'SP000000030','肌苷口服液          ',15.3000 union
select 'SP000000031','黄芪精口服液        ',44.4000 union
select 'SP000000032','五加参蛤蚧精口服液  ',55.2000 union
select 'SP000000033','宝光风湿液          ',45.6000 union
select 'SP000000034','骨刺消痛液          ',28.5000 union
select 'SP000000035','三七药酒            ',72.0000 union
select 'SP000000036','颈痛灵药酒          ',42.9000 union
select 'SP000000037','头痛定糖浆          ',40.5000 union
select 'SP000000038','酸枣仁合剂          ',40.5000 union
select 'SP000000039','小儿热速清糖浆      ',20.7000 
delete scm_info where scm_id='SP000000018'
update scm_info set scm_lsj=50 where scm_id='SP000000013'
select * from history_scm_info