SQL SERVER触发器语法与使用示例(43)

一、触发器语法

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