例如,首先有两个表(学生表和班级表),向学生表中插入几条学生数据记录后,班级表中的班级人数自动变更。
班级人数有45人,插入学生数据后,班级人数应变成50
用触发器
示例:
use Testdb2
go
IF NOT OBJECT_ID('[t_Class]') IS NULL
DROP TABLE [t_Class]
GO
CREATE TABLE [t_Class]
([ID] int identity(1,1) primary key not null,
[Name] Nvarchar(20) null,
[Number] int null
)
go
INSERT [t_Class]
SELECT '一班',0 union all
SELECT '二班',0 union all
SELECT '三班',0
go
IF NOT OBJECT_ID('[t_Student]') IS NULL
DROP TABLE [t_Student]
GO
CREATE TABLE [t_Student]
([ID] int identity(1,1) primary key not null,
[Name] Nvarchar(20) null,
[ClassID] int not null
)
go
--select * from [t_Student]
alter TRIGGER Trigger_AutoCountStudentNumbersByClassID
ON dbo.[t_Student]
FOR INSERT,update
AS
Update [T_Class] set [Number]=
s.TNumber
from
(select count(1) as Tnumber,ClassID from [T_Student] group by ClassID)
s inner join [T_Class] c on c.[ID]=s.ClassID
where c.ID in (select [ClassID] from inserted group by ClassID)
go
--插入测试数据
--truncate table [T_Student]
--go
insert into [T_Student]
select '李洪治',1 union all
select '韦一笑',2 union all
select '吕不为',2
go
select * from [T_Student]
select * from [T_Class]
--结果
--ID Name ClassID
--1 李洪治 1
--2 韦一笑 2
--3 吕不为 2
----ID Name Number
----1 一班 1
----2 二班 2
----3 三班 0
同意楼上的.
同意楼上的回答,
1、触发器。
2、sql语句+事务处理
3、存储过程+事务处理
在表A上建触发器,当有数据插入A时,更新表B
使用触发器,sql语句如下:
create trigger insert_students after insert on students
for each row
as begin
update grade set students_num = select count(1) from studentsgroup by students_name
end
用触发器
班级表的人数一列应该三算出来的,不是手动更新的吧?