有两个表
一个是table1.一个是table2,table1(ypid,Rec)table2(ypid,fsdate)
他们都有共同的字段ypid(字符型)问,我想实现当table2每增加一条记录时table1的Rec将动态刷新为table2的这个ypid的记录数,如果table1没有对应记录就生成一条记录,下面是触发过程1.table2;里面现在刚生成一条记录内容如下table2(ypid, fsdate)
RE123 2009-12-12
RE123 2009-12-15
RE123 2009-12-18
....
RE888 2009-12-20....那么table1触发器应该有两个结果1.如果table1里面没有ypid='RE123' 的记录就自动生成一条记录
insert into table1(ypid,Rec) select 'RE123',1
2.如果table1有ypid='RE123' 的记录就刷新ypid='RE123' 的Rec为3以上table2表的记录生成后的table1的结果为
insert into table1(ypid,Rec) select 'RE123',1table1(ypid, REC)
RE123 3
RE888 1
一个是table1.一个是table2,table1(ypid,Rec)table2(ypid,fsdate)
他们都有共同的字段ypid(字符型)问,我想实现当table2每增加一条记录时table1的Rec将动态刷新为table2的这个ypid的记录数,如果table1没有对应记录就生成一条记录,下面是触发过程1.table2;里面现在刚生成一条记录内容如下table2(ypid, fsdate)
RE123 2009-12-12
RE123 2009-12-15
RE123 2009-12-18
....
RE888 2009-12-20....那么table1触发器应该有两个结果1.如果table1里面没有ypid='RE123' 的记录就自动生成一条记录
insert into table1(ypid,Rec) select 'RE123',1
2.如果table1有ypid='RE123' 的记录就刷新ypid='RE123' 的Rec为3以上table2表的记录生成后的table1的结果为
insert into table1(ypid,Rec) select 'RE123',1table1(ypid, REC)
RE123 3
RE888 1
on table2
for insert
as
begin
insert table1(ypid,REC)
select ypid,count(1) from inserted i
where not exists(select 1 from table1 where ypid=i.ypid)
group by ypid
update table1
set REC=REC+i.cnt
from (select ypid,count(1) as cnt from inserted i group by ypid) i
where table1.ypid=i.ypid
end
on table2
after insert,delete,update
as
update a set
rec = isnull(a.rec,0)+b.flag
from table1 as a
join (
select ypid,sum(flag) as flag
from (
select ypid,1 as flag from inserted
union all
select ypid,-1 as flag from deleted
) as t
group by ypid
having sum(flag)<>0
) as b
on a.ypid=b.ypid; insert table1
select ypid,flag
from (select ypid,count(*) as flag from inserted group by ypid) as a
where not exists(select * from table1 where ypid=a.ypid);
go
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2010-01-03 10:33:23
-------------------------------------
--> 生成测试数据: [table1]
IF OBJECT_ID('[table1]') IS NOT NULL DROP TABLE [table1]
CREATE TABLE [table1] (ypid varchar(5),Rec int)
--> 生成测试数据: [table2]
IF OBJECT_ID('[table2]') IS NOT NULL DROP TABLE [table2]
CREATE TABLE [table2] (ypid varchar(5),fsdate datetime)
--SQL查询如下:
go
create trigger tri_test
on table2
after insert,delete,update
as
update a set
rec = isnull(a.rec,0)+b.flag
from table1 as a
join (
select ypid,sum(flag) as flag
from (
select ypid,1 as flag from inserted
union all
select ypid,-1 as flag from deleted
) as t
group by ypid
having sum(flag)<>0
) as b
on a.ypid=b.ypid; insert table1
select ypid,flag
from (select ypid,count(*) as flag from inserted group by ypid) as a
where not exists(select * from table1 where ypid=a.ypid);
goINSERT INTO [table2]
SELECT 'RE123','2009-12-12' UNION ALL
SELECT 'RE123','2009-12-15' UNION ALL
SELECT 'RE123','2009-12-18' UNION ALL
SELECT 'RE888','2009-12-20';update table2 set ypid='RE888' WHERE ypid='RE123'DELETE TOP(1) FROM table2insert table2 select 'liang',GETDATE();select * from table1go
drop table table1,table2