--用视图+触发器,可以解决参数传递问题.--协助更新处理的视图,主要是为了参数传递
create view v_card
as
select *,what_do=cast('' as varchar),change_much=cast(0 as int)
from card
go--创建处理的触发器
create trigger t_update on v_card
instead of update
as
declare @i int
if update(kye) set @i=1
if update(kqk) if @i=1 goto lb_err else set @i=1
if update(used_many) if @i=1 goto lb_err else set @i=1
if update(kgive) if @i=1 goto lb_err else set @i=1
if update(kinte) if @i=1 goto lb_err else set @i=1
if @i is null returnselect mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
into #d from deleted
select mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
,id,card_type,what_do,change_much
into #i from inserted--写历史表
insert into history(card_id,card_type,what_do,change_much,last_much)
select i.id,i.card_type,i.what_do,i.change_much
,last_much=case when i.kye<>d.kye then d.kye
else case when i.kqk<>d.kqk then d.kqk
else case when i.kgive<>d.kgive then d.kgive
else case when i.kinte<>d.kinte then d.kinte
else d.used_many
end end end endfrom #i i join #d d on i.mid=d.mid--更新card
update card set kye=b.kye,kqk=b.kqk,used_many=b.used_many,
kgive=b.kgive,kinte=b.kinte,card_type=b.card_type
from card a join inserted b on a.id=b.idreturnlb_err:
RAISERROR('错误,不能同时更新多个字段',16,1)
go
create view v_card
as
select *,what_do=cast('' as varchar),change_much=cast(0 as int)
from card
go--创建处理的触发器
create trigger t_update on v_card
instead of update
as
declare @i int
if update(kye) set @i=1
if update(kqk) if @i=1 goto lb_err else set @i=1
if update(used_many) if @i=1 goto lb_err else set @i=1
if update(kgive) if @i=1 goto lb_err else set @i=1
if update(kinte) if @i=1 goto lb_err else set @i=1
if @i is null returnselect mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
into #d from deleted
select mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
,id,card_type,what_do,change_much
into #i from inserted--写历史表
insert into history(card_id,card_type,what_do,change_much,last_much)
select i.id,i.card_type,i.what_do,i.change_much
,last_much=case when i.kye<>d.kye then d.kye
else case when i.kqk<>d.kqk then d.kqk
else case when i.kgive<>d.kgive then d.kgive
else case when i.kinte<>d.kinte then d.kinte
else d.used_many
end end end endfrom #i i join #d d on i.mid=d.mid--更新card
update card set kye=b.kye,kqk=b.kqk,used_many=b.used_many,
kgive=b.kgive,kinte=b.kinte,card_type=b.card_type
from card a join inserted b on a.id=b.idreturnlb_err:
RAISERROR('错误,不能同时更新多个字段',16,1)
go
create table card(id int,card_type varchar(10)
,kye int,kqk int,used_many int,kgive int,kinte int)
insert card select 989,'钻石卡',5980,-800,50,300,200create table history(id int identity(1,1),card_id int
,what_do varchar(10),card_type varchar(10),last_much int,change_much int)
go--协助更新处理的视图,主要是为了参数传递
create view v_card
as
select *,what_do=cast('' as varchar),change_much=cast(0 as int)
from card
go--创建处理的触发器
create trigger t_update on v_card
instead of update
as
declare @i int
if update(kye) set @i=1
if update(kqk) if @i=1 goto lb_err else set @i=1
if update(used_many) if @i=1 goto lb_err else set @i=1
if update(kgive) if @i=1 goto lb_err else set @i=1
if update(kinte) if @i=1 goto lb_err else set @i=1
if @i is null returnselect mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
into #d from deleted
select mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
,id,card_type,what_do,change_much
into #i from inserted--写历史表
insert into history(card_id,card_type,what_do,change_much,last_much)
select i.id,i.card_type,i.what_do,i.change_much
,last_much=case when i.kye<>d.kye then d.kye
else case when i.kqk<>d.kqk then d.kqk
else case when i.kgive<>d.kgive then d.kgive
else case when i.kinte<>d.kinte then d.kinte
else d.used_many
end end end endfrom #i i join #d d on i.mid=d.mid--更新card
update card set kye=b.kye,kqk=b.kqk,used_many=b.used_many,
kgive=b.kgive,kinte=b.kinte,card_type=b.card_type
from card a join inserted b on a.id=b.idreturnlb_err:
RAISERROR('错误,不能同时更新多个字段',16,1)
go--更新测试
update v_card set kye=5780,what_do='消费',change_much=200
where id=989
goupdate v_card set kqk=-800,what_do='补款',change_much=300
where id=989
goupdate v_card set used_many=46,what_do='扣卡',change_much=4
where id=989
go--显示记录结果
select * from history
select * from card
go--删除测试环境
drop table card,history
drop view v_card/*--测试结果id card_id what_do card_type last_much change_much
----------- ----------- ---------- ---------- ----------- -----------
1 989 消费 钻石卡 5980 200
2 989 补款 钻石卡 50 300
3 989 扣卡 钻石卡 50 4(所影响的行数为 3 行)id card_type kye kqk used_many kgive kinte
----------- ---------- ----------- ----------- ----------- ----------- -----------
989 钻石卡 5780 -800 46 300 200(所影响的行数为 1 行)
--*/
create table card(id varchar(4),card_type varchar(20),kye float,kqk float,used_many float, kgive float,kinte float)
create table history(id int,card_id varchar(4),what_do varchar(20),card_type varchar(20),last_much float,change_much float)
insert into card
select '989','钻石卡',5980,-800,50,300,200
--函数get_id()
if exists(select * from sysobjects where name='get_id')
drop function get_id
gocreate function get_id()
returns int as
begin
declare @num int
select @num=count(*) from history
return @num+1
end
--触发器
if exists(select * from sysobjects where name='tu_card_update')
drop trigger tu_card_update
gocreate trigger tu_card_update
on card
for update
as
begin
declare @kye float,
@pre_kye float,
@kqk float,
@pre_kqk float,
@used_many float,
@pre_used_many float,
@id varchar(4),
@card_type varchar(20)
select @pre_kye=kye,@pre_kqk=kqk,@pre_used_many=used_many from deleted
select @kye=kye,@kqk=kqk,@used_many=used_many,@id=id,@card_type=card_type from inserted
if update(kye)
insert history
select dbo.get_id(),@id,'消费',@card_type,@pre_kye,@pre_kye-@kye
if update(kqk)
insert history
select dbo.get_id(),@id,'补款',@card_type,@pre_kqk,@kqk-@pre_kqk
if update(used_many)
insert history
select dbo.get_id(),@id,'扣卡',@card_type,@pre_used_many,@pre_used_many-@used_many
end
--测试 '989','钻石卡',5980,-800,50,300,200
update card set kye=5780,kqk=-500,used_many=46
where id='989'select * from card
select * from history
--清空历史记录表数据
delete from history
--删除表及函数
drop table card
drop table history
drop function get_id
/**
这个触发器有一个问题是,只要你修改了数据,哪怕值没有改变,它也往history表里插一条记录,你可以修改一下,做一下判断它的值有没有改变。
*/
create trigger t_update on v_card
instead of update
as
declare @i int
if update(kye) set @i=1 else set @i=0
if update(kqk) if @i<>0 goto lb_err else set @i=2
if update(used_many) if @i<>0 goto lb_err else set @i=3
if update(kgive) if @i<>01 goto lb_err else set @i=4
if update(kinte) if @i<>01 goto lb_err else set @i=5select mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte,id
into #d from deleted
select mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
,id,card_type,what_do,change_much
into #i from insertedif @i<>0
--写历史表
insert into history(card_id,card_type,what_do,change_much,last_much)
select i.id,i.card_type,i.what_do,i.change_much
,last_much=case @i when 1 then d.kye
when 2 then d.kqk
when 3 then d.used_many
when 4 then d.kgive
when 5 then d.kinte
end
from #i i join #d d on i.mid=d.mid--更新card
/*--如果不考虑id会被同时更新的情况,则用这句代替下面这名
update card set kye=b.kye,kqk=b.kqk,used_many=b.used_many,
kgive=b.kgive,kinte=b.kinte,card_type=b.card_type
from card a join inserted b on a.id=b.id
--*/--/*--考虑id可能会被同时更新
update card set kye=b.kye,kqk=b.kqk,used_many=b.used_many,
kgive=b.kgive,kinte=b.kinte,id=b.id,card_type=b.card_type
from card a join(
select i.kye,i.kqk,i.used_many,i.kgive,i.kinte
,i.id,i.card_type,d_id=d.id
from #i i join #d d on i.mid=d.mid
)b on a.id=b.d_id
--*/
returnlb_err:
RAISERROR('错误,不能同时更新多个字段',16,1)
go
--处理示例,注意,字段kye、kqk、used_many、kgive、kinte不能同时更新--测试数据
create table card(id int,card_type varchar(10)
,kye int,kqk int,used_many int,kgive int,kinte int)
insert card select 989,'钻石卡',5980,-800,50,300,200create table history(id int identity(1,1),card_id int
,what_do varchar(10),card_type varchar(10),last_much int,change_much int)
go--协助更新处理的视图,主要是为了参数传递
create view v_card
as
select *,what_do=cast('' as varchar),change_much=cast(0 as int)
from card
go--创建处理的触发器
create trigger t_update on v_card
instead of update
as
declare @i int
if update(kye) set @i=1 else set @i=0
if update(kqk) if @i<>0 goto lb_err else set @i=2
if update(used_many) if @i<>0 goto lb_err else set @i=3
if update(kgive) if @i<>01 goto lb_err else set @i=4
if update(kinte) if @i<>01 goto lb_err else set @i=5select mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte,id
into #d from deleted
select mid=identity(int,1,1),kye,kqk,used_many,kgive,kinte
,id,card_type,what_do,change_much
into #i from insertedif @i<>0
--写历史表
insert into history(card_id,card_type,what_do,change_much,last_much)
select i.id,i.card_type,i.what_do,i.change_much
,last_much=case @i when 1 then d.kye
when 2 then d.kqk
when 3 then d.used_many
when 4 then d.kgive
when 5 then d.kinte
end
from #i i join #d d on i.mid=d.mid--更新card
/*--如果不考虑id会被同时更新的情况,则用这句代替下面这名
update card set kye=b.kye,kqk=b.kqk,used_many=b.used_many,
kgive=b.kgive,kinte=b.kinte,card_type=b.card_type
from card a join inserted b on a.id=b.id
--*/--/*--考虑id可能会被同时更新
update card set kye=b.kye,kqk=b.kqk,used_many=b.used_many,
kgive=b.kgive,kinte=b.kinte,id=b.id,card_type=b.card_type
from card a join(
select i.kye,i.kqk,i.used_many,i.kgive,i.kinte
,i.id,i.card_type,d_id=d.id
from #i i join #d d on i.mid=d.mid
)b on a.id=b.d_id
--*/
returnlb_err:
RAISERROR('错误,不能同时更新多个字段',16,1)
go--更新测试
update v_card set kye=5780,what_do='消费',change_much=200
where id=989
goupdate v_card set kqk=-500,what_do='补款',change_much=300
where id=989
goupdate v_card set used_many=46,what_do='扣卡',change_much=4
where id=989
go--显示记录结果
select * from history
select * from card
go--删除测试环境
drop table card,history
drop view v_card/*--测试结果
id card_id what_do card_type last_much change_much
----------- ----------- ---------- ---------- ----------- -----------
1 989 消费 钻石卡 5980 200
2 989 补款 钻石卡 -800 300
3 989 扣卡 钻石卡 50 4(所影响的行数为 3 行)id card_type kye kqk used_many kgive kinte
------- ---------- -------- -------- ----------- -------- -----------
989 钻石卡 5780 -500 46 300 200(所影响的行数为 1 行)
--*/
if object_id('tr_card') is not null drop trigger tr_card
gocreate trigger tr_card on card for insert,update,delete asselect id, --id
card_type, --卡类型
-kye as value, --值,合计用
kye as lastvalue, --值,记录最后值用。
item = cast('kye' as varchar(10)) --业务项目
into #t
from deleted
union all select id,card_type,-kqk,kqk,'kqk' from deleted
union all select id,card_type,-used_many,used_many,'used_many' from deleted
union all select id,card_type,-kgive,kgive,'kgive' from deleted
union all select id,card_type,-kinte,kinte,'kinte' from deleted
union all select id,card_type,kye,0,'kye' from inserted
union all select id,card_type,kqk,0,'kqk' from inserted
union all select id,card_type,used_many,0,'used_many' from inserted
union all select id,card_type,kgive,0,'kgive' from inserted
union all select id,card_type,kinte,0,'kinte' from insertedinsert into history
select id,
card_type,
case item when 'kye' then '消费'
when 'kqk' then '补款'
when 'used_many' then '扣卡'
when 'kgive' then 'kgive..'
when 'kinte' then 'kinte..'
end,
sum(lastvalue),
sum(value)
from #t
group by id,card_type,item
having sum(value) <> 0drop table #tgo