MSSQL数据库中建立触发器的实践

1. 触发器的介绍

触发器（trigger）是数据库系统提供的一种特殊的存储过程，它是一段能够自动执行的程序，能够在特定的时刻对数据库进行监视并响应它所监视到的操作。触发器通常与表有关，它能够在特定的表上自动运行，以便监视特定的事件，如INSERT、UPDATE或DELETE语句。触发器可以在数据库中执行一个操作，并像存储过程一样接受参数和返回值。

2. 在MSSQL数据库中建立触发器的步骤

2.1. 创建触发器

要在MSSQL数据库中创建触发器，需要使用CREATE TRIGGER语句。

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- trigger actions here
END

在这个语句中：

trigger_name是指要创建的触发器的名称。

table_name是指要创建触发器的表的名称。

AFTER INSERT, UPDATE, DELETE是指在何种情况下触发触发器。

BEGIN/END之间是要执行的代码，即触发器的主体。

2.2. 定义触发器的主体

在定义触发器的主体时，需要注意以下几点：

必须在BEGIN/END之间编写触发器主体代码。

主体代码必须被定义为一组SQL语句。

主体代码可以执行任何标准的SQL语句。

代码中可以包含IF/ELSE语句，用于根据某些条件控制触发器的行为。

可以使用INSERTED和DELETED临时表，这些临时表分别包含了在触发器触发的INSERT、UPDATE或DELETE操作中所影响的行。

2.3. 启用和禁用触发器

启用和禁用触发器可以使用ALTER TRIGGER语句。

如果要启用触发器，请使用：ALTER TRIGGER trigger_name ENABLE。

如果要禁用触发器，请使用：ALTER TRIGGER trigger_name DISABLE。

3. 一个实例

下面的例子演示了如何在表格中创建一个触发器，以便在每次插入、更新或删除行时记录操作。触发器基于PID（产品ID）列。每当INSERT、UPDATE或DELETE发生时，触发器会在文本文件中记录操作。

CREATE TRIGGER tr_ProductsChange
ON Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @op CHAR(6)
    IF @@ROWCOUNT = 0
        RETURN
    IF EXISTS (SELECT * FROM inserted)
    BEGIN
        IF EXISTS (SELECT * FROM deleted)
            SET @op = 'UPDATE'
        ELSE
            SET @op = 'INSERT'
    END
    ELSE
        SET @op = 'DELETE'
    --
    DECLARE @outfile VARCHAR(255),
            @OLEresult INT
    SET @outfile = 'D:\Data\Products_Changes.txt'
    --
    DECLARE @PID INT,
            @SQL NVARCHAR(4000),
            @Date VARCHAR(50),
            @User VARCHAR(50)
    DECLARE c CURSOR FOR 
    SELECT PID FROM inserted UNION ALL SELECT PID FROM deleted
    OPEN c
    FETCH c INTO @PID
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        SELECT @User = SYSTEM_USER,
               @Date = CONVERT(CHAR(25), GETDATE(), 120)
        IF @op IN ('UPDATE', 'DELETE')
            SELECT @User = SYSTEM_USER,
                   @Date = CONVERT(CHAR(25), GETDATE(), 120)
        SET @SQL = 
        'echo ------------------------------------------------------------------------------- >> "' + @outfile + '"'
        EXEC master..xp_cmdshell @SQL, NO_OUTPUT
        SET @SQL = 
        'echo ' + @op + ' ' + CONVERT(VARCHAR(10), @PID) + ' @ ' + @Date + ' by ' + @User + ' >> "' + @outfile + '"'
        EXEC master..xp_cmdshell @SQL, NO_OUTPUT
        --
        FETCH c INTO @PID
    END
    CLOSE c
    DEALLOCATE c
END
GO

以上例子中，我们创建了一个名为tr_ProductsChange的触发器，并将其应用于Products表。在AFTER INSERT、UPDATE或DELETE之后，触发器将会自动执行。在触发器中，我们设置了一个游标（CURSOR），来针对INSERTED和DELETED临时表中的数据来记录操作到文件。其中，使用了xp_cmdshell来执行操作系统命令，从而将记录写入到文件（D:\Data\Products_Changes.txt）中。

4. 总结

MSSQL数据库中的触发器可以在指定表添加、删改内容时，自动触发哩某些行动，将会给程序员带来很大的便利，提高编码的效率和程序的稳定性。同时，在定义触发器主体时，我们也要注意触发器的性能和安全问题，尽量避免使用不必要的操作，保护数据库的安全和稳定。