34,590
社区成员
发帖
与我相关
我的任务
分享
表A
ID(标识) NAME(名称) WEIGHT(重量)
1 Sylar 100
2 Sylar 200
3 Sylar 300
4 Back 900
5 Back 800
表B
ID(表B的标识) NAME(名称) R_NUM(记录条数) WEIGHT_SUM(总重量)
1 Sylar 3 600
2 Back 2 1700
A表主要2个字段,名字和重量。
B表相应的统计A表里NAME字段相同的记录有多少条(保存在R_NUM),以及他们的总重量是多少(保存在WEIGHT_SUM里)。
用触发器实现。
触发器,就是更新A表的时候,B表可以自动更新记录。
比如我更新了A表,添加了记录: 6 Sylar 100
则,B表的第一条记录自动更新为: 1 Sylar 4 700
create table a(ID int, NAME varchar(10), WEIGHT int)
create table b(ID int IDENTITY(1,1), NAME varchar(10),R_NUM int, WEIGHT_SUM int)
go
create trigger my_trig on a for insert , delete , update
as
begin
if exists(select 1 from inserted)
begin
if exists(select 1 from b where name = (select name from inserted))
begin
update b set R_NUM = (select count(1) from a where a.name = b.name) from b where b.name = (select name from inserted)
update b set WEIGHT_SUM = (select sum(a.WEIGHT) from a where a.name = b.name) from b where b.name = (select name from inserted)
end
else
insert into b (NAME , R_NUM ,WEIGHT_SUM) select a.name , count(1) , sum(a.WEIGHT) from a , inserted i where a.name = i.name group by a.name
end
else
begin
update b set R_NUM = (select count(1) from a where a.name = b.name) from b where b.name = (select name from deleted)
update b set WEIGHT_SUM = (select sum(a.WEIGHT) from a where a.name = b.name) from b where b.name = (select name from deleted)
end
end
go
insert into a values(1 ,'Sylar' ,100)
insert into a values(2 ,'Sylar' ,200)
insert into a values(3 ,'Sylar' ,300)
insert into a values(4 ,'Back' ,900)
insert into a values(5 ,'Back' ,800)
select * from b
drop table a,b
/*
ID NAME R_NUM WEIGHT_SUM
----------- ---------- ----------- -----------
1 Sylar 3 600
2 Back 2 1700
(所影响的行数为 2 行)
*/
--触发器的操作1
create table 化验室纱组(本厂编号 int,客户 int,色号 int,纱支 int)
create table 化验室布组(本厂编号 int,客户 int,色号 int,布类 int)
go
create trigger my_trig on 化验室纱组 for insert ,update ,delete
as
if not exists(select 1 from inserted)
delete 化验室布组 from deleted t where 化验室布组.本厂编号 = t.本厂编号
else if not exists(select 1 from deleted)
insert into 化验室布组(本厂编号 ,客户 ,色号) select 本厂编号 ,客户 ,色号 from inserted
else
update 化验室布组 set 客户 = t.客户 , 色号 = t.色号 from inserted t where 化验室布组.本厂编号 = t.本厂编号
go
--1、insert 对化验室纱组插入数据,然后查看化验室布组表的数据
insert into 化验室纱组 values(1 , 2 , 3 , 4)
insert into 化验室纱组 values(5 , 6 , 7 , 8)
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
1 2 3 NULL
5 6 7 NULL
(所影响的行数为 2 行)
*/
--2、update , 更改化验室纱组表中本厂编号=1的色号=6
update 化验室纱组 set 色号 = 6 where 本厂编号 = 1
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
1 2 6 NULL
5 6 7 NULL
(所影响的行数为 2 行)
*/
--3、delete 化验室纱组表中本厂编号=1的那条数据
delete from 化验室纱组 where 本厂编号 = 1
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
5 6 7 NULL
(所影响的行数为 1 行)
*/
drop table 化验室纱组 , 化验室布组