问Server:通过泛型触发器记录数据库更改

EN

-- Create the ChangeLog table
CREATE TABLE ChangeLog
(
    ID BIGINT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    COMMAND NCHAR(6) NOT NULL,
    CHANGED_DATE DATETIME2 DEFAULT GETDATE() NOT NULL,
    TABLE_NAME NVARCHAR(255) NOT NULL,
    COLUMN_NAMES TEXT NULL,
    COLUMN_OLD_VALUES TEXT NULL,
    COLUMN_NEW_VALUES TEXT NULL,
    USERNAME NVARCHAR(100) NOT NULL
)
GO

-- Create Trigger for Table to log changes
ALTER TRIGGER CHANGE_MyTableName
ON MyTableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Define which command was executed
    DECLARE @command CHAR(6)
    SET @command =
    CASE
        WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'UPDATE'
        WHEN EXISTS(SELECT * FROM inserted) THEN 'INSERT'
        WHEN EXISTS(SELECT * FROM deleted) THEN 'DELETE'
        ELSE NULL
    END

    -- Define variables
    DECLARE @seperator NVARCHAR(2)
    SET @seperator = ', '

    DECLARE @column_name NVARCHAR(255)
    DECLARE @column_names VARCHAR(MAX)
    DECLARE @column_old_values VARCHAR(MAX)
    DECLARE @column_new_values VARCHAR(MAX)

    -- Select the column names to populate @column_names separated by ', '
    SELECT @column_names = COALESCE(@column_names + @seperator, '') + COLUMN_NAME 
    FROM information_schema.columns 
    WHERE table_name = 'MyTableName'

    -- Create cursor to populate @column_old_values or/and @column_new_values
    DECLARE CURSOR_FOR_COLUMN_NAMES CURSOR FOR 
        -- Select the column name as a string
        SELECT sys.columns.name AS ColumnName
        FROM sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id
        WHERE tables.name = 'MyTableName'

    -- Perform the first fetch.
    OPEN CURSOR_FOR_COLUMN_NAMES 

    FETCH NEXT FROM CURSOR_FOR_COLUMN_NAMES INTO @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @command = 'UPDATE' or @command = 'DELETE'
            -- Select the old values to populate @column_old_values separated by ', '
            SELECT @column_old_values = COALESCE(@column_old_values + @seperator, '') + @column_name 
            FROM deleted

        IF @command = 'UPDATE' or @command = 'INSERT'
            -- Select the new values to populate @column_new_values separated by ', '
            SELECT @column_new_values = COALESCE(@column_new_values + @seperator, '') + @column_name 
            FROM inserted

        FETCH NEXT FROM CURSOR_FOR_COLUMN_NAMES INTO @column_name
    END

    CLOSE CURSOR_FOR_COLUMN_NAMES
    DEALLOCATE CURSOR_FOR_COLUMN_NAMES

    -- Insert into the ChangeLog table
    IF @command = 'UPDATE'
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, COLUMN_NAMES, COLUMN_OLD_VALUES, COLUMN_NEW_VALUES, USERNAME)
        VALUES (@command, GETDATE(), 'MyTableName', @column_names, @column_old_values, @column_new_values, USER_NAME())

    IF @command = 'INSERT'
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, COLUMN_NAMES, COLUMN_NEW_VALUES, USERNAME)
        VALUES (@command, GETDATE(), 'MyTableName', @column_names, @column_new_values, USER_NAME())

    IF @command = 'DELETE'
        INSERT INTO ChangeLog (COMMAND, CHANGED_DATE, TABLE_NAME, COLUMN_NAMES, COLUMN_OLD_VALUES, USERNAME)
        VALUES (@command, GETDATE(), 'MyTableName', @column_names, @column_old_values, USER_NAME())
END
GO