Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Triggers for Auditing


Triggers for Auditing

Author
Message
Tiago Andrade e Silva
Tiago Andrade e Silva
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Won't that break if you have more than one row updated at the same time??
Tiago Andrade e Silva
Tiago Andrade e Silva
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Tiago Andrade e Silva
Tiago Andrade e Silva
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Anatol Romanov
Anatol Romanov
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 56

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


Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8352 Visits: 11592

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
G.R.Prithiviraj Kulasingham
G.R.Prithiviraj Kulasingham
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 367

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/
SA-1
SA-1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 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


G.R.Prithiviraj Kulasingham
G.R.Prithiviraj Kulasingham
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 367

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/
David Korb
David Korb
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 311

> 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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search