一、触发器语法
1、创建触发器:
CREATE TRIGGER <触发器名> ON <表名> <INSTEAD OF | AFTER> <UPDATE | INSERT | DELETE>
AS
语句块
GO
2、修改触发器语法:
ALTER TRIGGER <触发器名> ON <表名> <INSTEAD OF | AFTER> <UPDATE | INSERT | DELETE>
AS
语句块
GO
3、删除触发器语法: DROP TRIGGER <触发器名>
4、查看触发器语法:
1)查看数据库所有触发器:
USE <数据库名>
GO
SELECT * FROM SysObjects WHERE Xtype='TR'
GO
2)查看触发器内容:
USE <数据库名>
GO
EXEC sp_helptext <触发器名称>
GO
二、示例:
1)在Emp员工表插入名为“任我行”,部门id为5,名称为“新部门”,并将新部门名称自动更新至部门表Det
--在Emp表建立触发器(INSERT触发器-after)
CREATE TRIGGER Tri_Insert_Emp ON Emp AFTER INSERT
AS
BEGIN
IF NOT EXISTS(SELECT * FROM Det WHERE ID=(SELECT D_Id FROM Inserted ))
--通过Inserted逻辑表找到插入的部门ID,再与部门表里的ID进行对比,无则执行下一个SQL语句
INSERT INTO Det VALUES((SELECT D_Id FROM Inserted ),'新部门')
END
GO
--在Emp表插入数据,测试触发器
INSERT INTO Emp VALUES('任我行','男',5)
GO
2)删除部门表Det里的“人事部”,同时在员工表Emp里的删除为人事部的员工
--在部门表Det,建立触发器(DELETE触发器-after)
CREATE TRIGGER Tri_del_Det ON Det AFTER DELETE
AS
DELETE FROM Emp WHERE D_Id=(SELECT ID FROM DELETED)
GO
--在部门表Det删除人事部测试触发器
DELETE FROM Det WHERE ID=2
GO
3)删除部门表Det里的开发部,删除前先判断部门下是否有员工,有则不删,无则删除
--在部门表Det,建立触发器(DELETE触发器-Insdead of )
CREATE TRIGGER Tri_del_Det ON Det INSTEAD OF DELETE
AS
BEGIN
IF NOT EXISTS(SELECT * FROM Emp WHERE D_Id=(SELECT ID FROM DELETED))
DELETE FROM Det WHERE ID=(SELECT ID FROM DELETED)
END
GO
--在部门表删除总经办,测试触发器
DELETE Det WHERE ID=1
GO--结果是数据未删除,原因是员工表里部为总经办这个部门下有员工,不符合条件,事务将回滚
--在部门表删除开发部门,测试触发器
DELETE Det WHERE ID=4
GO--结果是数据删除,原因是员工表里部为开发部这个部门下有无工
4)将部门表Det里的总经办部门ID改为5,同时更新员工表里的部门D_Id
--在部门表Det,建立触发器(UPDATE触发器-INSTEAD OF)
CREATE TRIGGER Tri_up_Det ON Det INSTEAD OF UPDATE
AS
BEGIN
UPDATE Dep SET ID=(SELECT ID FROM INSERTED)
WHERE ID=(SELECT ID FROM DELETED)--更新部门表Dep
UPDATE Emp SET D_Id=(SELECT ID FROM INSERTED)
WHERE D_Id=(SELECT ID FROM DELETED)--更新员工表Emp
END
GO
--将部门表Det里的部门总经办,ID变更为5
UPDATE Det SET ID=5 WHERE ID=1
GO