[表A]有字段ID,Count,Right
[表B]有字段ID,Right
问如何令当[表B]删除,添加,修改数据时,[表A]对应的ID号的COUNT自动为[表B]的记录总数,Right为表B的Right等于1的总数
例如:
[表B] [表A] 自动为以下数
ID Right ID Count Right
1 1 1 2 1
1 0 2 2 2
2 1
2 1
[表B]有字段ID,Right
问如何令当[表B]删除,添加,修改数据时,[表A]对应的ID号的COUNT自动为[表B]的记录总数,Right为表B的Right等于1的总数
例如:
[表B] [表A] 自动为以下数
ID Right ID Count Right
1 1 1 2 1
1 0 2 2 2
2 1
2 1
解决方案 »
- SQL2005 installation failed谁能帮帮我
- SQL2000里有什么办法可以只让新建的用户访问数据库,而管理员(SA)无法访问呀~
- 存储过程能否返回多个值?
- 求句sql,谢谢
- 安装 mssql2000 的sp4 还需要安装之前低版本的补丁?如 sp3
- 如果是你,如何建库???
- 在sql server7.0中 select top 10 distinct name from p_user取前面10个名字不一样的记录,该用什么命令,top 和 distinct不能同时用
- 有请各位SQL高手,关于数据库中DBO对象的问题
- 如何用SQL语句写Access数据库中的日期型字段?(急!!!!!!!!!11)
- 新手求指教 关于2005连不上数据库的问题
- 求解:脏数据解释??
- 我从dbf文件中读取数据,为什么会报下面的错误"OLE DB 提供程序 'MSDASQL' 报错"
gocreate table A(ID int,[Count] int,[Right] int)
gocreate trigger cfq on B
for insert,update,delete
asif @@rowcount=0 returndeclare @count_ins int
declare @count_del int
select @count_ins=count(*) from inserted
select @count_del=count(*) from deletedif(@count_ins>0 and @count_del=0)
begin
delete from A
insert into A select ID,(select count(*) from B) as [Count],count(*) as [right] from B where [Right]=1 group by ID
endif(@count_ins>0 and @count_del>0)
begin
delete from A
insert into A select ID,(select count(*) from B) as [Count],count(*) as [right] from B where [Right]=1 group by ID
endif(@count_ins=0 and @count_del>0)
begin
delete from A
insert into A select ID,(select count(*) from B) as [Count],count(*) as [right] from B where [Right]=1 group by ID
end
goinsert into B select
1,1 union all select
1,0 union all select
2,1 union all select
2,1
select * from A
godelete from A where ID=1
select * from A
goupdate B set ID=3
select * from A
godrop trigger cfq
drop table B
drop table A