Hi all:
I believe Server Side programming is much better than client side but I got a question.
what effect has Trigger on DB. does it has a overload on performance or its ok, or even its worth?!

like logging on tables change such as User_Creator, Creat_DateTime, User_Modifier, Modifier_DateTime & ...

Always:),
Hovik Melkomian.

__________________
Always,
Hovik Melkomian.

It all depends on what you are trying to accomplish. Triggers do require some overhead. 1. Processing time, read/writes, etc. 2. Triggers are synchronous within the transaction. If the process is lengthy, the total time for the trigger will add to the user's total transaction time. I use them all the time with various levels of complexity, including complete field level auditing. The users never noticed the <1 second response difference. Just keep them as simple as possible.

In SQL 2005, Event Notification offers a secondary asynchronous method instead of triggers.


Adam Gossage
Lake Wylie, SC, USA

Hi AGossage,

I'm nearly finished with a .NET application. I'd like to put some auditing in place on the DB that the app uses, for historical purposes and to audit who is making the changes. I was wondering if you could give me some general tips on do's and don't's, maybe some of the things that you tried and didn't work out and some that do the trick.

Thanks,
Richard

There are two ways to store the audit information. All in one audit table or one audit per physical table. I have only used the all in one approach, because the amount of data to be stored is minimal.

There are many examples of Audit/History tables on the net. Try searching for Audit Trigger.

I use the all in one approach and have written a script that scripts out the triggers. I wanted to avoid using dynamic sql to insert into the audit table. The script makes it easy to add triggers to all tables for a given catalog.schema.

Since this is my personal script, it does not include FOR INSERT, DELETE. You will need to modify to use in SQL 2000, because I placed the audit information in a separate schema. It contains the script for a table that holds the all of the tables for a given catalog.schema. Therefore, you can set which tables to script, etc. Let me know if you have any questions. Or, if you can add to it, send me the updates.

-- SCRIPT --
SET NOCOUNT ON
--CREATE SCHEMA Audit

IF (OBJECT_ID('Audit.History') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('Audit.History'), 'IsTable') = 1) DROP TABLE Audit.History
GO
CREATE TABLE Audit.History
(
      HistoryIdentifier bigint NOT NULL IDENTITY CONSTRAINT PK_History PRIMARY KEY NONCLUSTERED
    , HistoryTableName varchar(128) NOT NULL
    , HistoryTableIdentifier bigint NOT NULL
    , HistoryColumnName varchar(128) NOT NULL
    , HistoryNewValue varchar(8000) NULL
    , HistoryOldValue varchar(8000) NULL
    , HistoryUser varchar(200) NOT NULL CONSTRAINT DF_HistoryUser DEFAULT(USER_NAME())
    , HistoryTimestamp datetime NOT NULL CONSTRAINT DF_HistoryTimestamp DEFAULT(GETDATE())
)
GO

IF (OBJECT_ID('Audit.ScriptTable') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('Audit.ScriptTable'), 'IsTable') = 1) DROP TABLE Audit.ScriptTable
GO
CREATE TABLE Audit.ScriptTable
(
        ScriptTableIdentifier int NOT NULL IDENTITY CONSTRAINT PK_ScriptTable PRIMARY KEY NONCLUSTERED
      , ScriptTableSchema varchar(128) NOT NULL
      , ScriptTableName varchar(128) NOT NULL
      , ScriptTableIdentifierColumn varchar(128) NOT NULL
      , ScriptTableInclude tinyint NOT NULL CONSTRAINT DF_ScriptTableInclude DEFAULT(1)
)
GO

-- Load ScriptTable with current BASE TABLES
INSERT INTO Audit.ScriptTable (ScriptTableSchema, ScriptTableName, ScriptTableIdentifierColumn)
SELECT
      a.TABLE_SCHEMA
    , a.TABLE_NAME
    , b.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES a
    JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME
WHERE a.TABLE_TYPE = 'BASE TABLE'
    AND a.TABLE_SCHEMA != 'Audit'
    AND b.ORDINAL_POSITION IN
        (
            SELECT TOP 1 ORDINAL_POSITION
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = a.TABLE_SCHEMA
                AND TABLE_NAME = b.TABLE_NAME
            ORDER BY ORDINAL_POSITION
        )
GO

IF (OBJECT_ID('Audit.usp_GenerateTriggers', 'P') IS NOT NULL) DROP PROCEDURE Audit.usp_GenerateTriggers
GO
CREATE PROCEDURE Audit.usp_GenerateTriggers
AS
SET NOCOUNT ON
BEGIN
    DECLARE @nstrTABLE_CATALOG nvarchar(128)
    DECLARE @nstrTABLE_SCHEMA nvarchar(128)
    DECLARE @nstrTABLE_NAME nvarchar(128)

    DECLARE cursorTables CURSOR
    READ_ONLY
    FOR
        SELECT
              a.TABLE_CATALOG
            , a.TABLE_SCHEMA
            , a.TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES a
            JOIN Audit.ScriptTable b ON a.TABLE_SCHEMA = b.ScriptTableSchema AND a.TABLE_NAME = b.ScriptTableName
        WHERE a.TABLE_TYPE = 'BASE TABLE'
            AND a.TABLE_SCHEMA != 'Audit'
            AND b.ScriptTableInclude = 1

    OPEN cursorTables

    FETCH NEXT FROM cursorTables INTO @nstrTABLE_CATALOG, @nstrTABLE_SCHEMA, @nstrTABLE_NAME
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        DECLARE @nstrTEMP nvarchar(4000)
        SET @nstrTEMP = ''

        SET @nstrTEMP = N'IF (OBJECT_ID(''dbo.tr_Audit_' + @nstrTABLE_NAME + ''',''TR'') IS NOT NULL) DROP TRIGGER dbo.tr_Audit_' + @nstrTABLE_NAME + CHAR(10) + 'GO' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'CREATE TRIGGER ' + @nstrTABLE_SCHEMA + '.tr_Audit_' + @nstrTABLE_NAME + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'ON ' + @nstrTABLE_SCHEMA + '.' + @nstrTABLE_NAME + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'AFTER UPDATE' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'AS' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'/************************************************** ******************************' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'** SQL Data Audit Script For SQL 2005' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'** Trigger Name: dbo.tr_Audit_' + @nstrTABLE_NAME + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'** Table: ' + @nstrTABLE_SCHEMA + '.' + @nstrTABLE_NAME + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'** Generated: ' + CONVERT(nvarchar(20), GETDATE()) + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'************************************************ ********************************/' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'SET NOCOUNT ON' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'BEGIN'
        PRINT @nstrTEMP

        DECLARE @nstrCOLUMN_NAME_IDENTIFIER nvarchar(128)
        DECLARE @nstrCOLUMN_NAME nvarchar(128)
        SET @nstrCOLUMN_NAME_IDENTIFIER = NULL

        DECLARE cursorColumns CURSOR READ_ONLY
        FOR
            SELECT
                  a.COLUMN_NAME
                , b.ScriptTableIdentifierColumn
            FROM INFORMATION_SCHEMA.COLUMNS a
                JOIN Audit.ScriptTable b ON a.TABLE_SCHEMA = b.ScriptTableSchema AND a.TABLE_NAME = b.ScriptTableName
            WHERE a.TABLE_SCHEMA = @nstrTABLE_SCHEMA
                AND a.TABLE_NAME = @nstrTABLE_NAME
            ORDER BY a.ORDINAL_POSITION

        OPEN cursorColumns

        FETCH NEXT FROM cursorColumns INTO @nstrCOLUMN_NAME, @nstrCOLUMN_NAME_IDENTIFIER

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @nstrTEMP = CHAR(9) + N'IF (UPDATE(' + @nstrCOLUMN_NAME + '))' + CHAR(10)
            SET @nstrTEMP = @nstrTEMP + CHAR(9) + CHAR(9) + N'INSERT INTO Audit.History (HistoryTableName, HistoryTableIdentifier, HistoryColumnName, HistoryNewValue, HistoryOldValue)' + CHAR(10)
            SET @nstrTEMP = @nstrTEMP + CHAR(9) + CHAR(9) + N'SELECT ''' + @nstrTABLE_SCHEMA + '.' + @nstrTABLE_NAME + ''', i.' + @nstrCOLUMN_NAME_IDENTIFIER + ', ''' + @nstrCOLUMN_NAME + ''', i.' + @nstrCOLUMN_NAME + ', d.' + @nstrCOLUMN_NAME + CHAR(10)
            SET @nstrTEMP = @nstrTEMP + CHAR(9) + CHAR(9) + N'FROM inserted i' + CHAR(10)
            SET @nstrTEMP = @nstrTEMP + CHAR(9) + CHAR(9) + CHAR(9) + N'JOIN deleted d ON i.' + @nstrCOLUMN_NAME_IDENTIFIER + '=d.' + @nstrCOLUMN_NAME_IDENTIFIER + CHAR(10)
            SET @nstrTEMP = @nstrTEMP + CHAR(9) + CHAR(9) + N'WHERE i.' + @nstrCOLUMN_NAME + '!=d.' + @nstrCOLUMN_NAME + CHAR(10)
            SET @nstrTEMP = @nstrTEMP + CHAR(9) + CHAR(9) + CHAR(9) + N'OR (i.' + @nstrCOLUMN_NAME + ' IS NULL AND d.' + @nstrCOLUMN_NAME + ' IS NOT NULL)' + CHAR(10)
            SET @nstrTEMP = @nstrTEMP + CHAR(9) + CHAR(9) + CHAR(9) + N'OR (i.' + @nstrCOLUMN_NAME + ' IS NOT NULL AND d.' + @nstrCOLUMN_NAME + ' IS NULL)' + CHAR(10)
            PRINT @nstrTEMP

            FETCH NEXT FROM cursorColumns INTO @nstrCOLUMN_NAME, @nstrCOLUMN_NAME_IDENTIFIER
        END

        CLOSE cursorColumns
        DEALLOCATE cursorColumns

        SET @nstrTEMP = N'END' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'SET NOCOUNT OFF' + CHAR(10)
        SET @nstrTEMP = @nstrTEMP + N'GO' + CHAR(10) + CHAR(10)
        PRINT @nstrTEMP

        FETCH NEXT FROM cursorTables INTO @nstrTABLE_CATALOG, @nstrTABLE_SCHEMA, @nstrTABLE_NAME
    END

    CLOSE cursorTables
    DEALLOCATE cursorTables

    RETURN
END
GO

-- List of Tables to script
SELECT * FROM Audit.ScriptTable WHERE ScriptTableInclude = 1
EXEC Audit.usp_GenerateTriggers
GO

Adam Gossage
Lake Wylie, SC, USA