SQL Server触发器 #

触发器特点：
├── 自动执行
├── 不能直接调用
├── 与表关联
├── 可以访问inserted/deleted表
└── 可以回滚操作

SQL Server触发器类型：
├── DML触发器
│   ├── AFTER触发器（后触发）
│   └── INSTEAD OF触发器（替代触发）
├── DDL触发器
│   └── 数据库/服务器级别
└── LOGON触发器
    └── 登录时触发

-- 创建AFTER INSERT触发器
CREATE TRIGGER tr_users_after_insert
ON users
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 记录插入操作
    INSERT INTO users_audit (user_id, action, action_time)
    SELECT id, 'INSERT', GETDATE() FROM inserted;
    
    PRINT 'User inserted';
END
GO

-- 创建AFTER UPDATE触发器
CREATE TRIGGER tr_users_after_update
ON users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 记录更新操作
    INSERT INTO users_audit (user_id, action, old_value, new_value, action_time)
    SELECT 
        i.id,
        'UPDATE',
        d.name,
        i.name,
        GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.id = d.id;
END
GO

-- 创建AFTER DELETE触发器
CREATE TRIGGER tr_users_after_delete
ON users
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 记录删除操作
    INSERT INTO users_audit (user_id, action, action_time)
    SELECT id, 'DELETE', GETDATE() FROM deleted;
END
GO

-- 创建INSTEAD OF INSERT触发器
CREATE TRIGGER tr_users_instead_insert
ON users
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 验证数据
    IF EXISTS (SELECT 1 FROM inserted WHERE name IS NULL OR name = '')
    BEGIN
        RAISERROR('Name cannot be empty', 16, 1);
        RETURN;
    END
    
    -- 执行插入
    INSERT INTO users (name, email, status)
    SELECT name, email, ISNULL(status, 1) FROM inserted;
END
GO

-- 创建INSTEAD OF UPDATE触发器
CREATE TRIGGER tr_users_instead_update
ON users
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 不允许更新id
    IF UPDATE(id)
    BEGIN
        RAISERROR('Cannot update id', 16, 1);
        RETURN;
    END
    
    -- 执行更新
    UPDATE u
    SET 
        u.name = i.name,
        u.email = i.email,
        u.status = i.status
    FROM users u
    INNER JOIN inserted i ON u.id = i.id;
END
GO

-- 创建INSTEAD OF DELETE触发器
CREATE TRIGGER tr_users_instead_delete
ON users
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 检查是否有关联数据
    IF EXISTS (
        SELECT 1 FROM orders o
        INNER JOIN deleted d ON o.user_id = d.id
    )
    BEGIN
        RAISERROR('Cannot delete user with orders', 16, 1);
        RETURN;
    END
    
    -- 执行删除
    DELETE FROM users
    WHERE id IN (SELECT id FROM deleted);
END
GO

-- inserted表：包含新数据
-- deleted表：包含旧数据

/*
操作类型    | inserted表 | deleted表
------------|------------|----------
INSERT      | 新数据     | 空
UPDATE      | 新数据     | 旧数据
DELETE      | 空         | 旧数据
*/

-- 查看变化的数据
CREATE TRIGGER tr_users_track_changes
ON users
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 插入操作
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        PRINT 'INSERT operation';
        SELECT * FROM inserted;
    END
    
    -- 更新操作
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        PRINT 'UPDATE operation';
        SELECT 'Old' AS type, * FROM deleted
        UNION ALL
        SELECT 'New' AS type, * FROM inserted;
    END
    
    -- 删除操作
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        PRINT 'DELETE operation';
        SELECT * FROM deleted;
    END
END
GO

-- 使用UPDATE()函数检查列是否被更新
CREATE TRIGGER tr_users_check_update
ON users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 检查特定列是否被更新
    IF UPDATE(salary)
    BEGIN
        PRINT 'Salary was updated';
        
        -- 记录薪资变更
        INSERT INTO salary_history (user_id, old_salary, new_salary, change_date)
        SELECT 
            i.id,
            d.salary,
            i.salary,
            GETDATE()
        FROM inserted i
        INNER JOIN deleted d ON i.id = d.id;
    END
    
    -- 检查多列
    IF UPDATE(name) OR UPDATE(email)
    BEGIN
        PRINT 'Name or email was updated';
    END
END
GO

-- 使用COLUMNS_UPDATED检查多列
CREATE TRIGGER tr_users_columns_updated
ON users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 检查第1、2、3列是否被更新
    IF COLUMNS_UPDATED() & 14 > 0  -- 14 = 2+4+8
    BEGIN
        PRINT 'Columns 2, 3, or 4 were updated';
    END
END
GO

-- 创建数据库级别DDL触发器
CREATE TRIGGER tr_prevent_drop_table
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Cannot drop tables in this database';
    ROLLBACK;
END
GO

-- 记录DDL操作
CREATE TRIGGER tr_log_ddl_operations
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO ddl_audit (event_type, object_name, sql_text, event_time)
    SELECT 
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)'),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'),
        GETDATE();
END
GO

-- 创建服务器级别DDL触发器
CREATE TRIGGER tr_prevent_create_login
ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
    PRINT 'Cannot create logins';
    ROLLBACK;
END
GO

-- 记录服务器级别DDL操作
CREATE TRIGGER tr_log_server_ddl
ON ALL SERVER
FOR DDL_SERVER_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO master.dbo.server_ddl_audit (event_type, sql_text, event_time)
    SELECT 
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'),
        GETDATE();
END
GO

-- 使用EVENTDATA获取事件信息
CREATE TRIGGER tr_show_eventdata
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    DECLARE @data XML;
    SET @data = EVENTDATA();
    
    SELECT 
        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)') AS EventType,
        @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(100)') AS PostTime,
        @data.value('(/EVENT_INSTANCE/SPID)[1]', 'INT') AS SPID,
        @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(100)') AS ServerName,
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)') AS LoginName,
        @data.value('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(100)') AS UserName,
        @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(100)') AS DatabaseName,
        @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(100)') AS SchemaName,
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)') AS ObjectName,
        @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)') AS ObjectType,
        @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)') AS CommandText;
END
GO

-- 创建登录触发器
CREATE TRIGGER tr_limit_logins
ON ALL SERVER
FOR LOGON
AS
BEGIN
    -- 限制特定用户在特定时间登录
    IF ORIGINAL_LOGIN() = 'restricted_user' 
       AND DATEPART(HOUR, GETDATE()) NOT BETWEEN 9 AND 17
    BEGIN
        PRINT 'Login not allowed outside business hours';
        ROLLBACK;
    END
END
GO

-- 查看表的触发器
EXEC sp_helptrigger 'users';

-- 查看触发器定义
EXEC sp_helptext 'tr_users_after_insert';

-- 查看触发器信息
SELECT 
    name,
    type_desc,
    create_date,
    modify_date,
    is_disabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID('users');

-- 禁用触发器
DISABLE TRIGGER tr_users_after_insert ON users;

-- 禁用所有触发器
ALTER TABLE users DISABLE TRIGGER ALL;

-- 启用触发器
ENABLE TRIGGER tr_users_after_insert ON users;

-- 启用所有触发器
ALTER TABLE users ENABLE TRIGGER ALL;

-- 修改触发器
ALTER TRIGGER tr_users_after_insert
ON users
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    PRINT 'Modified trigger';
END
GO

-- 删除触发器
DROP TRIGGER tr_users_after_insert;

-- 删除DDL触发器
DROP TRIGGER tr_prevent_drop_table ON DATABASE;

-- 删除服务器级别触发器
DROP TRIGGER tr_prevent_create_login ON ALL SERVER;

触发器设计原则：
├── 保持触发器简短
├── 避免递归触发器
├── 使用SET NOCOUNT ON
├── 正确处理多行操作
├── 避免在触发器中使用事务
└── 考虑使用约束替代触发器

-- 正确处理多行操作
CREATE TRIGGER tr_users_multirow
ON users
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 正确：使用集合操作
    INSERT INTO users_audit (user_id, action)
    SELECT id, 'INSERT' FROM inserted;
    
    -- 错误：假设只有一行
    -- DECLARE @id INT;
    -- SELECT @id = id FROM inserted;
END
GO

-- 检查是否递归触发
-- 数据库选项：RECURSIVE_TRIGGERS

-- 查看设置
SELECT name, is_recursive_triggers_on
FROM sys.databases;

-- 禁用递归触发器
ALTER DATABASE mydb SET RECURSIVE_TRIGGERS OFF;