SQL Server在CentOS上的触发器使用指南

一、前提：在CentOS上安装SQL Server

在CentOS上使用触发器前，需先完成SQL Server的安装与配置。以下是简要步骤：

1. 更新系统并安装依赖：运行sudo yum update -y更新系统，安装wget、openssl11-devel、libaio等依赖包（确保后续安装顺利）。
2. 添加SQL Server YUM存储库：根据CentOS版本（7/8）下载对应的存储库文件（如mssql-release.repo），并导入Microsoft GPG密钥（sudo rpm --import https://packages.microsoft.com/keys/microsoft.asc）。
3. 安装SQL Server软件包：使用sudo yum install -y msodbcsql17 unixodbc-devel安装客户端工具，再通过sudo /opt/mssql/bin/mssql-conf setup配置SQL Server实例（设置SA账户密码等）。
4. 启动服务并验证：运行sudo systemctl start mssql-server启动服务，sudo systemctl enable mssql-server设置开机自启；使用sqlcmd -S localhost -U sa -P YourPassword连接实例，执行SELECT @@VERSION;确认安装成功。

二、触发器基础概念

触发器（Trigger）是SQL Server中的特殊存储过程，与表关联，当表发生INSERT、UPDATE、DELETE操作时自动触发执行。其核心特点包括：

• 自动触发：无需手动调用，由数据变更事件激活。
• 逻辑表支持：通过INSERTED（存储新数据，INSERT/UPDATE后）和DELETED（存储旧数据，DELETE/UPDATE前）临时表获取变更数据。
• 类型分类：
  • AFTER触发器（默认）：在DML操作完成后执行，用于数据审计、级联更新等场景。
  • INSTEAD OF触发器：替代原始DML操作执行，用于复杂视图更新、数据验证拦截等场景。

三、触发器创建步骤与语法

1. 基本语法

CREATE TRIGGER trigger_name
ON table_name
{ FOR | AFTER | INSTEAD OF }  -- 触发时机
{ INSERT, UPDATE, DELETE }   -- 触发事件
AS
BEGIN
    SET NOCOUNT ON;           -- 减少网络流量，提升性能
    -- 触发器逻辑（可使用INSERTED、DELETED表）
END;

2. 示例：AFTER触发器（记录数据变更）

假设有employees表（id、name、salary、last_updated），创建触发器在更新薪资时记录变更日志：

-- 创建日志表
CREATE TABLE employee_salary_log (
    log_id INT IDENTITY(1,1) PRIMARY KEY,
    employee_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    change_time DATETIME
);

-- 创建AFTER触发器
CREATE TRIGGER trg_employee_salary_update
ON employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 仅当salary字段变更时记录
    IF UPDATE(salary)
    BEGIN
        INSERT INTO employee_salary_log (employee_id, old_salary, new_salary, change_time)
        SELECT 
            d.id, 
            d.salary, 
            i.salary, 
            GETDATE()
        FROM DELETED d
        JOIN INSERTED i ON d.id = i.id;
    END;
END;

验证：更新员工薪资后，查询employee_salary_log表即可看到变更记录。

四、常见应用场景与示例

1. 数据验证：禁止删除已完成订单

CREATE TRIGGER tr_prevent_delete_completed_orders
ON orders
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- 检查是否有已完成订单
    IF EXISTS (SELECT 1 FROM DELETED WHERE status = '已完成')
    BEGIN
        RAISERROR('无法删除已完成订单！', 16, 1);  -- 抛出错误
        ROLLBACK TRANSACTION;                   -- 回滚事务
        RETURN;
    END;
    ELSE
    BEGIN
        DELETE FROM orders WHERE id IN (SELECT id FROM DELETED);  -- 允许删除未完成订单
    END;
END;

2. 级联更新：用户姓名变更同步到订单

CREATE TRIGGER tr_sync_user_name_to_orders
ON users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 仅当姓名变更时同步
    IF UPDATE(name)
    BEGIN
        UPDATE o
        SET customer_name = i.name
        FROM orders o
        JOIN INSERTED i ON o.user_id = i.id
        JOIN DELETED d ON i.id = d.id
        WHERE i.name <> d.name;  -- 避免不必要的更新
    END;
END;

3. 数据同步：返利记录同步到提醒表

CREATE TRIGGER tr_sync_rebate_to_notice
ON BBD_FANLI_LS
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 将返利记录同步到提醒表
    INSERT INTO BBD_FLZHBDTX (user_id, order_no, amount, tx_time)
    SELECT 
        user_id, 
        order_no, 
        rebate_amount, 
        rebate_time
    FROM INSERTED;
END;

4. 审计日志：记录用户账户变更

CREATE TRIGGER tr_user_account_update_log
ON user_account
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO account_log (user_id, old_balance, new_balance, change_time, operation)
    SELECT 
        d.user_id,
        d.balance AS old_balance,
        i.balance AS new_balance,
        GETDATE(),
        'UPDATE'
    FROM DELETED d
    JOIN INSERTED i ON d.id = i.id;
END;

五、触发器管理

1. 查看表上的触发器

-- 查看指定表的所有触发器
SELECT * FROM sys.triggers WHERE parent_id = OBJECT_ID('your_table_name');

-- 查看数据库中所有触发器
SELECT * FROM sys.triggers;

2. 查看触发器定义

-- 查看触发器的创建语句
EXEC sp_helptext 'trigger_name';

3. 修改触发器

-- 使用ALTER TRIGGER修改触发器逻辑
ALTER TRIGGER trg_employee_salary_update
ON employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 新的逻辑（如增加日志字段）
    INSERT INTO employee_salary_log (employee_id, old_salary, new_salary, change_time, changed_by)
    SELECT 
        d.id, 
        d.salary, 
        i.salary, 
        GETDATE(),
        SYSTEM_USER  -- 记录操作用户
    FROM DELETED d
    JOIN INSERTED i ON d.id = i.id;
END;

4. 删除触发器

-- 删除指定触发器
DROP TRIGGER trigger_name ON table_name;

六、注意事项

1. 性能影响：避免在触发器中执行复杂查询或大量数据操作，防止拖慢数据库性能。
2. 事务处理：触发器在DML操作的事务中执行，若触发器抛出错误，整个事务将回滚。
3. 递归触发器：SQL Server默认允许递归触发（触发器触发自身），可通过sp_configure 'nested triggers', 0禁用。
4. WITH ENCRYPTION：若需保护触发器代码，可在创建时添加WITH ENCRYPTION选项，但需注意密钥管理。
5. 测试验证：在生产环境部署前，务必在测试环境充分测试触发器逻辑，避免意外数据变更。