三张表t1,t2,t3,表结构一样。t1和t2的数据通过上传Excel表获得,t3的数据是根据t1或t2的变化而更新。例如(字段a)t3.a=t1.a+t2.a。有说用触发器的,触发器完全搞不懂啊,怎么在asp.net调用都不知道?或者直接写个SQL语句?求各位帮忙,有个例子最好了。能不用触发器还是不用吧。谢谢了。之前也有帖子 http://topic.csdn.net/u/20120822/16/be529e82-1665-446a-b2f4-9406523344dc.html?seed=537905183&r=79476162#r_79476162
解决方案 »
- 关于调用其它软件编写的dll的问题
- IIS指向后 唯独Login页面打不开
- 模板页问题
- 请高手指点:文件删除是使用相对路径还是绝对路径呢?File.Delete的问题
- 配置错误
- 在线等待,.NET页面上如何控制默认按钮的焦点位置。
- 示例程序不能运行,什么原因?谢谢
- 急急!!!我写了一个循环打印一个表单,需单击一个按钮打印出来(成纸),请问怎么写这段代码阿?
- 对asp.net,HTML代码段中的Page_load(SRR AS OBJECT,E AS EVENTARGS)函数的困惑。
- 怎么在当前页面中给引用过来的用户控件中的下拉列表框赋值???
- 动态加载文章和对应的评论,怎么传递文章ID
- 如何解决Sql数据库连接不能正常关闭?
--触发器是不需要你操作的,当你执行新增,更新,删除时自动调用的,给你举个例子
if object_id('t1') is not null drop table t1
if object_id('t2') is not null drop table t2
if object_id('t3') is not null drop table t3
create table t1(id int primary key,a int not null)
create table t2(id int primary key,a int not null)
create table t3(id int primary key,a int not null)
go
create trigger t1_trg
on t1
for insert,update,delete
as
--新增,更新
if exists(select 1 from inserted)
begin
update t3 set a=t1.a+isnull(t2.a,0)
from inserted i
inner join t1 on i.id=t1.id
left join t2 on i.id=t2.id
where i.id=t1.id and i.id=t3.id
insert into t3
select t1.id,t1.a+isnull(t2.a,0)
from inserted i
inner join t1 on i.id=t1.id
left join t2 on i.id=t2.id
where not exists(select 1 from t3 where i.id=t3.id)
end
--删除
if exists(select 1 from deleted)
and not exists (select 1 from inserted)
begin
update t3 set a=t1.a+isnull(t2.a,0)
from inserted i
inner join t1 on i.id=t1.id
left join t2 on i.id=t2.id
where i.id=t3.id
delete t3
from deleted d
where t3.id=d.id
and not exists(select 1 from t1 where t1.id=t3.id and t1.id=d.id)
and not exists(select 1 from t2 where t2.id=t3.id and t2.id =d.id)
end
go
create trigger t2_trg
on t2
for insert,update,delete
as
--新增,更新
if exists(select 1 from inserted)
begin
update t3 set a=t2.a+isnull(t1.a,0)
from inserted i
inner join t2 on i.id=t2.id
left join t1 on i.id=t1.id
where i.id=t2.id and i.id=t3.id
insert into t3
select t2.id,t2.a+isnull(t1.a,0)
from inserted i
inner join t2 on i.id=t2.id
left join t1 on i.id=t1.id
where not exists(select 1 from t3 where i.id=t3.id)
end
--删除
if exists(select 1 from deleted)
and not exists (select 1 from inserted)
begin
update t3 set a=t2.a+isnull(t1.a,0)
from inserted i
inner join t2 on i.id=t2.id
left join t1 on i.id=t1.id
where i.id=t3.id
delete t3
from deleted d
where t3.id=d.id
and not exists(select 1 from t1 where t1.id=t3.id and t1.id=d.id)
and not exists(select 1 from t2 where t2.id=t3.id and t2.id =d.id)
end
go
--测试数据
insert into t1 values(1,2)
select * from t3
insert into t2 values(1,3)
select * from t3;
update t1 set a=5 where id=1
select * from t3
delete t1;
delete t2
select * from t3;
--对应每条语句后的结果
/*
id a
----------- -----------
1 2id a
----------- -----------
1 5id a
----------- -----------
1 8id a
----------- -----------
*/