create table a_1
(
[id] char (20),
total int
)
create table a_2
(
[id] char (20),
total int
)
有以上两表。
写一个触发器,实现:
对a_1进行insert,在a_2中,如果存在和a_1中刚插入的id相同的id,则对a_2中的这个id相应的total进行累加,如果没有,则在a_2中插入a_1中新插入的字段
对a_1进行delete,则在a_2中相同的id相应的total上减去a_1中刚删除的那个total
假设a_1为:
001 10
002 8
003 7
001 2
004 90
002 3
001 22
a_2为:
001 34
002 11
003 7
004 90
insert:
insert a_1 values('001',100)
insert后:
a_1为:
001 10
002 8
003 7
001 2
004 90
002 3
001 22
001 100
a_2为:
001 134
002 11
003 7
004 90
delete:
delete a_1
where a_1.[id]='001' and a_1.total = 10
delete后:
a_1为:
002 8
003 7
001 2
004 90
002 3
001 22
001 100
a_2为:
001 124
002 11
003 7
004 90
code:
create table a_1
(
[id] char (20),
total int
)
create table a_2
(
[id] char (20),
total int
)
create trigger trigger_1
on a_1
for insert , delete
as
if exists (select * from inserted) -- 判断是否进行insert操作
begin
if exists
(
select 'true'
from inserted
join a_2 on inserted.[id] =a_2.[id]
)
begin
declare @id_1 char(20),@val_1 int
select @id_1 = [id] , @val_1 = total from inserted
update a_2
set a_2.total = a_2.total + @val_1
where @id_1 = a_2.[id]
end
else
begin
declare @id_2 char(20),@val_2 int
select @id_2 = [id] , @val_2 = total from inserted
insert a_2 values(@id_2, @val_2)
end
end
else -- delete操作
begin
declare @id_3 char(20) , @val_3 int
select @id_3 = [id] , @val_3 = total from deleted
update a_2
set a_2.total = a_2.total - @val_3
where @id_3 = a_2.[id]
end
select * from a_1
select * from a_2
insert a_1 values('001',10)
insert a_1 values('002',9)
insert a_1 values('001',40)
insert a_1 values('003',73)
insert a_1 values('002',11)
select * from a_1
select * from a_2
delete a_1
where a_1."id" = '001' and a_1.total = 10
select * from a_1
select * from a_2
第一次写触发器,不免方法较笨,如果谁有更好的方法,还望分享分享。
posted on 2009-11-25 19:57
zhaoyg 阅读(220)
评论(0) 编辑 收藏 引用 所属分类:
SQL Server学习笔记