Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 16th, 2007, 11:40 AM
Friend of Wrox
Points: 3,558, Level: 25
Points: 3,558, Level: 25 Points: 3,558, Level: 25 Points: 3,558, Level: 25
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: California, USA
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default Trigger effects

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.
Reply With Quote
  #2 (permalink)  
Old January 16th, 2007, 11:51 AM
Authorized User
 
Join Date: Dec 2006
Location: Lake Wylie, SC, USA.
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old January 16th, 2007, 01:07 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old January 16th, 2007, 01:40 PM
Authorized User
 
Join Date: Dec 2006
Location: Lake Wylie, SC, USA.
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
After Effects CS2 Plugin - newbie mattybennett C++ Programming 0 August 22nd, 2007 04:06 PM
Trigger fhillipo SQL Server 2000 1 March 20th, 2007 07:44 AM
Effects of Browser Back Button prafullprashant ASP.NET 1.0 and 1.1 Basics 0 November 23rd, 2005 02:32 AM
Multiple Button Effects Bob Bedell Dreamweaver (all versions) 2 August 12th, 2003 07:10 PM



All times are GMT -4. The time now is 11:35 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.