今天学习了编写了一个简单的触发器.以下是对该触发器的编写的基本思路:
首先数据库中有两张表,一张WorkInfo表用来保存员工信息,字段有id,username,dptid.username是员工姓名,dptid是所属部门ID.另一张是dptInfo表,用来保存部门信息,有三个字段,id,dptname,dptworker.dptname是部门名,dptworker是员工数量.
当增加了一个员工时 ,触发触发器使得该员工所在部门的人数自动加一;当修改了一个员工的部门时,触发触发器使得该员工原来部门的人数减一,该员工现属部门人数加一.
编写触发器之前,先介绍两个基本的概念:
inserted,deleted是两个虚拟表,inserted保存的是insert或update之后影响的记录形成的表,deleted保存的是delete或update之前所影响的记录形成的表.
以下是对WorkInfo表的触发器代码:
CREATE
TRIGGER
tri_InsertCount
ON
dbo.WorkInfo
FOR INSERT , update , delete
AS declare @id int , @count int , @dptid int , @count1 int
select @id = dptid from inserted
select @count = count ( * ) from WorkInfo where dptid = @id
select @dptid = dptid from deleted
select @count1 = count ( * ) from WorkInfo where dptid = @dptid
update dptInfo set dptworker = @count where id = @id
update dptInfo set dptworker = @count1 where id = @dptid
FOR INSERT , update , delete
AS declare @id int , @count int , @dptid int , @count1 int
select @id = dptid from inserted
select @count = count ( * ) from WorkInfo where dptid = @id
select @dptid = dptid from deleted
select @count1 = count ( * ) from WorkInfo where dptid = @dptid
update dptInfo set dptworker = @count where id = @id
update dptInfo set dptworker = @count1 where id = @dptid