--
--3.8.2节示例
--
--创建部门员工表
CREATE TABLE Department
(
DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName nvarchar(20) NOT NULL,
EmployeeCount int NOT NULL DEFAULT(0)
)
GO
INSERT INTO Department(DepartmentName)
VALUES('财务部')
GO
CREATE TABLE Employee
(
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(10) NOT NULL,
DepartmentID int NOT NULL FOREIGN KEY REFERENCES Department(DepartmentID)
)
--
--3.8.3节示例
--
--创建触发器
CREATE TRIGGER UpdateDepartment_EmployeeCount
ON Employee
AFTER INSERT,DELETE
AS
IF(EXISTS(SELECT * FROM inserted)) --执行的是插入操作
BEGIN
UPDATE Department
SET EmployeeCount=EmployeeCount+1 --插入操作则对应部门的员工数+1
WHERE DepartmentID=(SELECT DepartmentID FROM inserted)
END
IF(EXISTS(SELECT * FROM deleted)) --执行的是删除操作
BEGIN
UPDATE Department
SET EmployeeCount=EmployeeCount-1 --删除操作则对应部门的员工数-1
WHERE DepartmentID=(SELECT DepartmentID FROM deleted)
END
--验证触发器是否生效
SELECT * FROM Department --初始情况下财务部的员工数为0
INSERT INTO Employee
VALUES(N'何欢',1) --插入1名财务部的员工
INSERT INTO Employee
VALUES(N’晏婉’,1) --再插入1名财务部的员工
SELECT * FROM Department --现在财务部的员工数为2
DELETE FROM Employee --删除1名财务部的员工
WHERE EmployeeName=N'何欢'
SELECT * FROM Department --现在财务部的员工数为1
DROP TRIGGER UpdateDepartment_EmployeeCount
--
--3.8.4节示例
--
DISABLE TRIGGER UpdateDepartment_EmployeeCount --禁用触发器
ON dbo.Employee
DISABLE TRIGGER ALL ON dbo.Employee
ENABLE TRIGGER UpdateDepartment_EmployeeCount --启用触发器
ON dbo.Employee
