触发器

--用户表 触发器 (主要用于同步班级人数)

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Trig_UserInfo' and xtype='TR')
DROP TRIGGER Trig_UserInfo
GO
CREATE TRIGGER Trig_UserInfo ON UserInfo
FOR UPDATE , INSERT , DELETE
AS
IF UPDATE(ClassId) --添加人员 或者 改变了班级
BEGIN
--原班级人数 -1
UPDATE ClassInfo SET StudentCount = StudentCount-1 WHERE Id IN (SELECT ClassId FROM DELETED)
--新班级人数 +1
UPDATE ClassInfo SET StudentCount = StudentCount+1 WHERE Id IN (SELECT ClassId FROM INSERTED)
END


DECLARE @IsDelete BIT
IF NOT EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM DELETED)
SET @IsDelete = 1
ELSE
SET @IsDelete = 0

--删除用户
IF(@IsDelete=1)
BEGIN
--减少相对班级人数
UPDATE ClassInfo SET StudentCount = StudentCount-1 WHERE Id IN (SELECT ClassId FROM DELETED)
END
GO

 

整理参考自：http://www.cnblogs.com/away_yue/archive/2009/04/20/1439735.html