写两个触发器即可Create Trigger [Update_Main1] On Subone
FOR INSERT,DELETE
As
Update Main Set count=(Select Count(1) from Inserted) Where tbName='Subone'
GO
Create Trigger [Update_Main2] On Subtwo
FOR INSERT,DELETE
As
Update Main Set count=(Select Count(1) from Inserted) Where tbName='Subtwo'
GO
FOR INSERT,DELETE
As
Update Main Set count=(Select Count(1) from Inserted) Where tbName='Subone'
GO
Create Trigger [Update_Main2] On Subtwo
FOR INSERT,DELETE
As
Update Main Set count=(Select Count(1) from Inserted) Where tbName='Subtwo'
GO
或者直接在修改subone和subtwo的时候修改main。触发器的语法我不记得了,可以查下帮助。
用客户端比较容易。
每次insert into subone成功以后,就update main set count=count+1 where id=1
每次delete from subone where id= 成功以后,就update main set count=count-1 where id=1
subtwo也作同样处理。
每次insert into subtwo成功以后,就update main set count=count+1 where id=2
每次delete from subtwo where id= 成功以后,就update main set count=count-1 where id=2
id int identity,
tbName NVarchar(100),
count int
)
create table Subone(
id int identity,
fValue NVarchar(100),
)
create table Subtwo(
id int identity,
fValue NVarchar(100),
)
insert into main values('Subone',0)
insert into main values('Subtwo',0)
CREATE TRIGGER tr_alterCount
on Subone
after insert,delete
as
update main set count=(select count(*) from Subone)
where tbName='Subone'
go
CREATE TRIGGER tr_alterCount1
on Subtwo
after insert,delete
as
update main set count=(select count(*) from Subone)
where tbName='Subtwo'
goinsert into Subone values('ad')
select * from main
ON Subone
FOR INSERT, DELETE
UPDATE Main Set [count] = (SELECT COUNT(*) FROM Subone) WHERE tbName = N'Subone'
GO
CREATE TRIGGER TRG2
ON Subone
FOR INSERT, DELETE
UPDATE Main Set [count] = (SELECT COUNT(*) FROM Subtwo) WHERE tbName = N'Subtwo'
GO
ON Subone
FOR INSERT, DELETE
AS
UPDATE Main Set [count] = (SELECT COUNT(*) FROM Subone) WHERE tbName = N'Subone'
GO
CREATE TRIGGER TRG2
ON Subone
FOR INSERT, DELETE
AS
UPDATE Main Set [count] = (SELECT COUNT(*) FROM Subtwo) WHERE tbName = N'Subtwo'
GO
FOR INSERT,DELETE
As
Update Main Set count=(Select Count(1) from Subone) Where tbName='Subone'
GO
Create Trigger [Update_Main2] On Subtwo
FOR INSERT,DELETE
As
Update Main Set count=(Select Count(1) from Subtwo) Where tbName='Subtwo'
GO
as
select 1 as id,'Subone' as tbName,count(*) from Subone
union all
select 2 as id,'Subtwo' as tbName,count(*) from Subtwo
...