表的数据依据另外两张表的更新而更新

表的数据根据另外两张表的更新而更新
三张表t1，t2，t3，表结构一样。t1和t2的数据通过上传Excel表获得，t3的数据是根据t1或t2的变化而更新。例如（字段a）t3.a=t1.a+t2.a。有说用触发器的，触发器完全搞不懂啊，怎么在asp.net调用都不知道？或者直接写个SQL语句？求各位帮忙，有个例子最好了。能不用触发器还是不用吧。谢谢了。之前也有帖子 http://topic.****.net/u/20120822/16/be529e82-1665-446a-b2f4-9406523344dc.html?seed=537905183&r=79476162#r_79476162

------解决方案--------------------

SQL code


--触发器是不需要你操作的，当你执行新增，更新，删除时自动调用的，给你举个例子
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

表的数据依据另外两张表的更新而更新

相关推荐