最近小得空闲,看了看SQL SERVER 触发器,简单写了一个小例子,做个记录。

--班级表

CREATE TABLE [dbo].[TGROUP](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GROUPNAME] [varchar](50) NULL,
[ACCOUNTUSER] [int] NULL,
[MEMO] [varchar](50) NULL
)

--学生表

 

CREATE TABLE [dbo].[TUSER](
[ID] [int] IDENTITY(1,1) NOT NULL primary key,
[NAME] [varchar](50) NULL,
[TEL] [varchar](50) NULL,
[OPERDATE] [datetime] NULL,
[OPERATOR] [varchar](50) NULL,
[DELEFLAG] [char](1) NULL,
[GROUPID] [int] NULL,
)

要求实现,当新增一个学生信息时,根据学生表中的GROUPID(班级ID), 相应班级表中的ACCOUNTUSER(学生人数)+1

新建触发器如下:

create trigger strTuser
on tuser
for insert
as
begin
update TGROUP set ACCOUNTUSER=ACCOUNTUSER+1
from TGROUP tg,inserted i
where tg.id=i.GROUPID
end

--测试数据

insert into TGROUP (GROUPNAME,ACCOUNTUSER) values('1年级1班',0)


insert into TUSER (NAME,TEL,OPERATOR,DELEFLAG,GROUPID)
values ('张玲玲','23611234','admin','0','1')
insert into TUSER (NAME,TEL,OPERATOR,DELEFLAG,GROUPID)
values ('李小一','23612235','admin','0','1')

--查看测试结果

select * from TUSER
select * from TGROUP

posted on 2015-06-05 14:23  forever of Provence  阅读(291)  评论(0编辑  收藏  举报