Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Triggers for Auditing Expand / Collapse
Author
Message
Posted Tuesday, November 1, 2005 5:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 13, 2007 3:28 AM
Points: 3, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tsilva/triggersforauditing.asp


Tiago Andrade e Silva
Post #234334
Posted Tuesday, November 1, 2005 11:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
Won't that break if you have more than one row updated at the same time??
Post #234370
Posted Thursday, November 24, 2005 3:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 13, 2007 3:28 AM
Points: 3, Visits: 1

Hi!

You are absolutely right!

but here is the correct version that works with multiple rows

 

 UPDATE [Table_Name]

 SET field1=i.field1,

field1=i.field1,

        'Put Here all the fields of the table
conn_string=i.conn_string,
  last_modified=getdate()  
 FROM inserted i
 INNER JOIN f6bo_Projectos  ON i.id_projecto = f6bo_Projectos.id_projecto




Tiago Andrade e Silva
Post #239419
Posted Thursday, November 24, 2005 4:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 13, 2007 3:28 AM
Points: 3, Visits: 1

opss ... posted in the middle of editing

here is the general version:

 UPDATE [Table_Name]

 SET field1=i.field1,

       field1=i.field1,

        'Put Here all the fields of the table
        last_modified=getdate()  
 FROM inserted i
 INNER JOIN [Table_Name] ON i.id= [Table_Name].id

 

Thanks for your remark.

Tiago




Tiago Andrade e Silva
Post #239420
Posted Monday, November 28, 2005 9:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:47 PM
Points: 17, Visits: 44

Many people are confused with this type of triggers (INSTEAD OF). This article is just another example.

The "solution" suggested in the article has a number of deficiencies.

The first was picked up as it was not able to handle multi-row updates.

Secondly, even after the suggested correction, the trigger will only do the job if the Id column's value is not changed. Consider this example:

CREATE TABLE Inmate( Id INT, Nickname VARCHAR(30), DateChanged DATETIME)

INSERT Inmate( Id, Nickname ) VALUES( 1, 'Rocky' )

-- Create INSTEAD OF UPDATE trigger on Inmate ...

UPDATE Inmate SET Id = 2 WHERE Id = 1

As a result, there will be 1 row in the table -

1, 'Rocky', NULL

The update statement from the trigger will not update this row because Inserted.Id = 2 and the join with Inmate is empty.

INSTEAD OF triggers are a delicate tool and should only be used when required. In this case - a normal AFTER trigger would do the job.

UPDATE Inmate SET DateChanged = GETDATE()

FROM Inmate

INNER JOIN Inserted ON Inmate.Id = Inserted.Id

Anatol Romanov
SQL Server MCP
Sydney Australia
www.fitbits.com.au

 

Post #240215
Posted Tuesday, November 29, 2005 2:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 5,969, Visits: 8,223

From the article:

One might be tempted to think that we would just need to change the last_modified field at the end of the trigger with the actual time and date and the magic would be done. But it's not so simple! The normal triggers which are called AFTER Triggers do not allow you to change the data on the virtual tables. So SQL Server 2000 has extended the power of triggers by letting you create a second kind of trigger, called an INSTEAD OF.

The author states -correctly- that AFTER triggers don't allow modification of the virtual tables. But he goes on to suggest that such modification is allowed in an INSTEAD OF trigger. This is nonsense - as the code he posted proves, the changes are made to the base table, not to the virtual tables. That can be done from both an AFTER and an INSTEAD OF trigger.

Two weaknesses in the suggested code have already been pointed out: the code fails on multi-row updates; the revised code (posted up-thread) fails if the primary key is changed. Let me add a third: the use of proprietary T-SQL code where ANSI standard code works just as well.

CREATE TRIGGER TriggerName ON [Table_Name]
AFTER UPDATE
AS
   UPDATE [Table_Name]

      SET  last_modified = CURRENT_TIMESTAMP 'this will do the magic
   WHERE EXISTS
 (SELECT *
   FROM   inserted
   WHERE inserted.id = [Table_Name].id)

If the table has a multi-column key, simply extend the WHERE clause of the subquery to include all the columns in the key.

Best, Hugo




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #240268
Posted Tuesday, November 29, 2005 4:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363

Author says:

What the INSTEAD OF trigger does is override the normal insert,delete or update statement and leave that responsibility to your Trigger. You will be responsible for inserting, deleting or updating the data to the database.

Not always! update on blob fields (text, ntext or image) will not fire triggers (This is applicable to both instead of and after triggers)

Additionally, as the standard statements are overwritten by instead of trigger, it is the responsibility of the trigger to find what fields are being updated.  If the table is modified through ad-hoc queries the trigger can't assume that all fields will be updated always.  If you assume, it will replace the fields  not updated by null.

UPDATE Table_Name

 SET field1= ISNULL(i.field1, Table_name.field1),

        'Put Here all the fields of the table
        last_modified=getdate()  
 FROM inserted i
 WHERE i.id= Table_Name.id

Should we consider the performance?  I think not.   We all know that triggers (both types) add significant overhead to the server.

So is there a better solution?

Always update the table through stored procedures.  Within the stored procedure, use the update last_modified field. Update through trigger is the last resort. Using stored procedure always has many added advantages too.  (Including security, performance and maintainability)

 



Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/
Post #240298
Posted Tuesday, November 29, 2005 8:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, Visits: 944

In our application we use AFTER triggers to maintain an audit trail. We need to maintain a history of changes to the data. We know that this causes a huge performance degradation. Is there a better alternative to this solution?

Has anyone implemented stored procedures to improve performance? And does it really speed things up?

The way I look at it the following steps would be involved:

1. Select the row to update

2. Compare the changed values

3. Update the record

4. Insert the audit record.

Any suggestions

Thanks

Post #240359
Posted Tuesday, November 29, 2005 8:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363

You can use stored procedure if all your Create/Update/delete operations happen through stored procedures only. 

Definitly it will improve the performance.  I have implemented certain operations (not auditing) through  triggers and later moved it to stored procedures.  I can see the difference.

If you are inserting the audit record to another table, I believe stored procedure may be the best option as you know (from the stored procedure parameters) what fields you are going to update. Specially on insert, without touching the virtual / actual table you can insert the new record to the audit table.

 



Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/
Post #240371
Posted Tuesday, November 29, 2005 10:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, June 8, 2013 9:40 AM
Points: 142, Visits: 286

> Not always! update on blob fields (text, ntext or image) will not fire triggers (This is applicable to both

> instead of and after triggers)

 

That's not true; I implemented audit triggers on tables with both text and image columns in them. The problem is that you can't refer to binary columns in the inserted and deleted tables. What you *can* do is join the inserted or deleted table to the base table and select data from the base table for the insert statement. In the case of the delete trigger, join deleted to the base audit table and select the newest audit row (for a particular ID) to get the final 'D' snapshot for that row.

Post #240429
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse