SQL-Server-触发器

JAY.LIN 收录于未分类

2025-03-16 约 817 字预计阅读 2 分钟

https://bing.ee123.net/img/rand?artid=146160060

SQL Server 触发器

在 SQL Server 中，触发器是一种特殊类型的存储过程，它会在特定事件发生时自动执行。触发器主要分为以下几种类型：

DML 触发器（Data Manipulation Language Triggers）
DDL 触发器（Data Definition Language Triggers）
登录触发器（Logon Triggers）

1. DML 触发器

DML 触发器用于响应数据操作语言（INSERT、UPDATE、DELETE）语句。这些触发器可以定义在表或视图上。

示例：创建一个 AFTER INSERT 触发器

sql

-- 创建一个示例表
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(100)
);
GO

-- 创建一个审计表
CREATE TABLE EmployeeAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    Name NVARCHAR(100),
    Position NVARCHAR(100),
    ActionTime DATETIME DEFAULT GETDATE()
);
GO

-- 创建一个 AFTER INSERT 触发器
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EmployeeAudit (EmployeeID, Name, Position)
    SELECT EmployeeID, Name, Position
    FROM inserted;
END;
GO

-- 测试触发器
INSERT INTO Employees (Name, Position) VALUES ('John Doe', 'Developer');
SELECT * FROM EmployeeAudit;

2. DDL 触发器

DDL 触发器用于响应数据定义语言（CREATE、ALTER、DROP）语句。它们可以定义在数据库级别或服务器级别，用于捕获和处理数据库对象的更改。

示例：创建一个数据库级别的 DDL 触发器

sql

-- 创建一个审计表
CREATE TABLE DDL_Audit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(100),
    ObjectName NVARCHAR(256),
    EventTime DATETIME DEFAULT GETDATE(),
    LoginName NVARCHAR(256)
);
GO

-- 创建一个数据库级别的 DDL 触发器
CREATE TRIGGER trgDatabaseDDL
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML;
    SET @EventData = EVENTDATA();

    INSERT INTO DDL_Audit (EventType, ObjectName, EventTime, LoginName)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)'),
        GETDATE(),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)')
    );
END;
GO

-- 测试触发器
CREATE TABLE TestTable (ID INT);
ALTER TABLE TestTable ADD Name NVARCHAR(100);
DROP TABLE TestTable;

SELECT * FROM DDL_Audit;

3. 登录触发器

登录触发器是在用户尝试连接到 SQL Server 实例时触发的。它们通常用于控制登录行为或记录登录活动。

示例：创建一个登录触发器

sql

-- 创建一个审计表
CREATE TABLE LogonAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    LoginName NVARCHAR(256),
    LoginTime DATETIME DEFAULT GETDATE(),
    ClientHost NVARCHAR(256)
);
GO

-- 创建一个登录触发器
CREATE TRIGGER trgLogon
ON ALL SERVER
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO master.dbo.LogonAudit (LoginName, LoginTime, ClientHost)
    VALUES (ORIGINAL_LOGIN(), GETDATE(), HOST_NAME());
END;
GO

-- 测试触发器
-- 注销并重新登录，然后检查审计表
SELECT * FROM master.dbo.LogonAudit;

总结

DML 触发器 ：用于响应 INSERT、UPDATE 和 DELETE 操作，可以定义在表或视图上。
DDL 触发器 ：用于响应 CREATE、ALTER 和 DROP 操作，可以定义在数据库级别或服务器级别。
登录触发器 ：用于响应用户登录事件，可以定义在服务器级别。

通过使用这些不同类型的触发器，你可以在 SQL Server 中实现复杂的业务逻辑、审计和安全控制。