数据库触发器操作

JY数据库触发器操作

USE JY
GO

1.说明：

1. 作用：实现主键和外键所不能保证的复杂参照完整性和数据一致性

2. 结果：
   触发器和引起触发器执行的语句会被当做一次事务处理，如果执行失败，
   自动回滚至事务执行前的状态

3. 功能：

   1.强化约束：能实现比check更复杂的约束，强制执行数据库中相关表的完整性
   2.跟踪数据变化：撤销或者回滚违反了引用完整性的操作，防止非法修改数据
   3.级联运行：级联修改数据库中相关的表，自动触发与之相关的操作
   4.返回自定义错误信息：可以返回信息，约束只能显示系统的标准错误信息

4. 注意：

   1.CREATE TRIGGER必须是批处理的第一条语句，且只能应用到一个表中
   2.与存储过程一样，当触发器触发时，需要向调用应用程序返回结果；若不需要返回结果，则不能在触发器中对变量赋值
   3.在同一条CREATE TRIGGER语句中，可以为多个事件定义相同的触发器操作
   4.触发器只能在当前数据库中创建，但可以引用当前数据库的外部对象
   5.如果一个表的外键在DELETE/UPDATE上定义了级联，则不能在该表上定义INSTEAD OF UPDATE/DELETE触发器
   6.约束可以实现预定数据完整性时，优先使用约束
   7.触发器不允许使用ALTER DATABASE、CREATE DATABASE和DROP DATABASE等语句
   8.允许嵌套和递归

2.创建AFTER触发器

• 语法格式

CREATE TRIGGER trigger_name
ON {table | view}
[WITH <ENCRPYPTION>]
{
    {FOR | AFTER | INSTEAD OF}
    {[DELETE] [,] [INSERT] [,] [UPDATE]}
    AS
        sql_statement[ , ...]  --触发器条件和操作
}

• 注意：

  1.AFTER是在完成所有的约束检查后执行
  2.INSTEAD OF可以解决外键约束问题，但不能解决是否为空、数据类型或标识列问题
  在执行约束前执行

• 示例1：

CREATE TRIGGER tg_updatereader
ON reader
AFTER UPDATE
AS
    BEGIN
        PRINT '已修改读者表reader的数据'
        SELECT reader_name AS '更新前' FROM DELETED
        SELECT reader_name AS '更新后' FROM INSERTED
    END
GO

UPDATE reader
SET reader_name = '哈哈晨'
WHERE reader_id = 'r0004'
GO

• 示例2：

CREATE TRIGGER tg_insertreader
ON reader
AFTER INSERT
AS
    BEGIN
        RAISERROR('不允许插入，操作已禁止', 1, 1)
        ROLLBACK TRANSACTION
    END
GO

INSERT INTO reader
VALUES ('r0010', '李佳佳', '女', '涉外教育系')
GO

• 示例3：

CREATE TRIGGER tg_deletereader
ON reader
AFTER DELETE
AS
    SELECT reader_id AS '已删除用户编号'
    FROM DELETE
GO

DELETE FROM reader
WHERE reader_id = 'r0009'
GO

• 示例4：

--进行插入、删除、更新record表时级联更新借阅记录
CREATE TRIGGER setview
ON record
FOR INSERT, UPDATE, DELETE
AS
    UPDATE book SET interview_times = interview_times + 1
        WHERE book_id = (SELECT book_id FROM INSERTED)
    UPDATE book SET interview_times = interview_times - 1
        WHERE book_id = (SELECT book_id FROM DELETED)
    PRINT '已自动更新book表中相应图书借阅次数'
GO

3.创建INSTEAD OF触发器

CREATE TRIGGER intg_updatereader
ON reader
INSTEAD OF UPDATE
AS
    PRINT '实际上并没有修改数据'
GO

UPDATE reader SET reader_name = '哈哈晨'
WHERE reader_id = 'r0004'
GO

4.管理触发器

• 修改触发器

ALTER TRIGGER tg_updatereader
ON reader
AFTER UPDATE
AS
    IF((SELECT count(*) FROM INSERTED) <> 0)
        PRINT '已修改reader表的数据'
    ELSE
        PRINT '不存在要更改的数据'
GO

UPDATE reader SET reader_name = 'Michael'
WHERE reader_id = 'r0012'
GO

• 删除触发器

DROP TRIGGER trigger_name[ , ...]

• 启用和禁用触发器

--禁用
ALTER TABLE reader
DISABLE TRIGGER tg_updatereader
--DISABLE TRIGGER tg_updatereader ON reader

--启用
ALTER TABLE reader
ENABLE TRIGGER tg_updatereader
--ENABLE TRIGGER tg_updatereader ON reader

• 查看触发器

SELECT *
FROM sysobjects WHERE TYPE = 'TR'