有三个表:A、B、C,三个表通过ID关联,
A表:
ID
Total
B表:
ID
Price
C表:
ID
NUM
A表的Total字段等于B表Price与C表的NUM的乘积总和,当B表或C表的值改变时,A表的值自动更新,请问这个触发器怎么写啊
A表:
ID
Total
B表:
ID
Price
C表:
ID
NUM
A表的Total字段等于B表Price与C表的NUM的乘积总和,当B表或C表的值改变时,A表的值自动更新,请问这个触发器怎么写啊
begin
update a set total = i.price * c.num from a , inserted i , c where a.id = i.id and a.id = c.id
endcreate trigger my_trigc on c for update
begin
update a set total = b.price * i.num from a , inserted i , b where a.id = i.id and a.id = b.id
end
当为insert的时候,如B,C表无关联,也应该为0.所以这两个触发器就不好写了.
忘记说了,B表和C表之间是有另外的ID进行关联的
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
drop table [dbo].[A]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[B]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[B]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[C]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[C]
GOCREATE TABLE [dbo].[A] (
[YWID] [int] NULL ,
[TOTAL] [numeric](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[B] (
[YWID] [int] NULL ,
[ID] [int] NULL ,
[PRICE] [numeric](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[C] (
[YWID] [int] NULL ,
[ID] [int] NULL ,
[NAMEID] [int] NULL ,
[NUM] [numeric](18, 0) NULL
) ON [PRIMARY]
GO--添加数据
insert into a select '1','0'
insert into a select '2','0'insert into b select '1','1','2'
insert into b select '2','2','3'insert into c select '1','1','1','2'
insert into c select '1','1','2','3'
insert into c select '2','2','1','4'
insert into c select '2','2','3','5'
--说明:a/b/c三表以YWID为关联字段,b/c表为主从表,以ID为关联字段
--触发器要求:当b/c两表有插入、修改、删除时自动更新a表的TOTAL字段的值
--计算规则:将b表的PRICE乘以c表NAMEID为1时的NUM字段值
/*以上数据为例时,a表TOTAL字段结果如下:
ID TOTAL
1 4
2 12
*/
ID TOTAL
1 4
2 12
怎么计算的
insert into b select '2','2','3'insert into c select '1','1','1','2'
insert into c select '1','1','2','3'
insert into c select '2','2','1','4'
insert into c select '2','2','3','5'
根据红色的是来计算的
[YWID] [int] NULL ,
[TOTAL] [numeric](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[B] (
[YWID] [int] NULL ,
[ID] [int] NULL ,
[PRICE] [numeric](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[C] (
[YWID] [int] NULL ,
[ID] [int] NULL ,
[NAMEID] [int] NULL ,
[NUM] [numeric](18, 0) NULL
) ON [PRIMARY]
GOgo
create trigger my_trigb on b for insert , update , delete
as
begin
update a set total = b.price * c.num from a , b ,c where a.ywid = b.ywid and b.id = c.id and c.nameid = 1 and (a.ywid = (select ywid from inserted) or a.ywid = (select ywid from deleted))
end
go
create trigger my_trigcT on c for insert , update , delete
as
begin
update a set total = b.price * c.num from a , b ,c where a.ywid = b.ywid and b.id = c.id and c.nameid = 1 and (a.ywid = (select ywid from inserted) or a.ywid = (select ywid from deleted))
end
go--添加数据
insert into a select '1','0'
insert into a select '2','0'insert into b select '1','1','2'
insert into b select '2','2','3'insert into c select '1','1','1','2'
insert into c select '1','1','2','3'
insert into c select '2','2','1','4'
insert into c select '2','2','3','5'
goselect * from a
/*
YWID TOTAL
----------- --------------------
1 4
2 12(所影响的行数为 2 行)
*/drop table a , b, c
CREATE TABLE [dbo].[A] (
[YWID] [int] NULL ,
[TOTAL] [numeric](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[B] (
[YWID] [int] NULL ,
[ID] [int] NULL ,
[PRICE] [numeric](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[C] (
[YWID] [int] NULL ,
[ID] [int] NULL ,
[NAMEID] [int] NULL ,
[NUM] [numeric](18, 0) NULL
) ON [PRIMARY]
GOgo
create trigger my_trigb on b for insert , update , delete
as
begin
update a set total = isnull(( select sum(b.price * c.num) from b ,c where a.ywid = b.ywid and b.id = c.id and c.nameid = 1),0) from a where (a.ywid = (select ywid from inserted) or a.ywid = (select ywid from deleted))
end
go
create trigger my_trigcT on c for insert , update , delete
as
begin
update a set total = isnull(( select sum(b.price * c.num) from b ,c where a.ywid = b.ywid and b.id = c.id and c.nameid = 1),0) from a where (a.ywid = (select ywid from inserted) or a.ywid = (select ywid from deleted))
end
go--添加数据
insert into a select '1','0'
insert into a select '2','0'insert into b select '1','1','2'
insert into b select '2','2','3'insert into c select '1','1','1','2'
insert into c select '1','1','2','3'
insert into c select '2','2','1','4'
insert into c select '2','2','3','5'
goselect * from a
/*
YWID TOTAL
----------- --------------------
1 4
2 12(所影响的行数为 2 行)
*/drop table a , b, c