Triggers for Auditing
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tsilva/triggersforauditing.asp
Tiago Andrade e Silva -
Won't that break if you have more than one row updated at the same time??
-
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 -
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 -
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 RomanovSQL Server MCPSydney Australiahttp://www.fitbits.com.au -
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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
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 Kulasinghamhttp://preethiviraj.blogspot.com/
-
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
-
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 Kulasinghamhttp://preethiviraj.blogspot.com/
-
> 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.
-
the solution has one more problem. the original update statement may update only one column. by forcing an update of all the columns, we will be taxing the servers. in case of the database implementation where the after trigger is used for auditing, this can be expensive. same in the case of implementations with replication
-
I am concerned about the incorrect and unsubstantiated statements made both in the article and by some of the posters. By now, most of the mistakes have been corrected, but I really wish people would triple-check their sources and code before posting.
There have been many articles written about triggers for auditing, and IMO, none can be used as a generic solution. The issue of auditing the entire record vs. just the changed fields (MUCH more difficult) is not usually addressed properly. The issue of trigger (or SP) overhead for auditing is almost never analyzed with real data. Rarely is the use of transaction log auditing discussed in any depth. The issue of auditing "meaningless" foreign keys (and not the "readable" value fom the related tables) is a serious concern in these solutions. The issue of "single table for all audits" vs. "one audit table per table" is another area that is usually ignored.
Like many others, I have created very complicated trigger-based and client app-based auditing solutions. I have had to design my database tables and app from the ground up with my own style of auditing solution in mind. In my case, that means I must have a single autoinc-integer primary key and timestamp for every table in the database. For my purposes, the client app must be able to instantly pull up a list of all the changes to any record in a database of hundreds of tables. The client app must also be able to generate a sequential list of all changes made to the database over an arbitrary time period, or made by an arbitrary user. Obviously, most multi-table trigger solutions would fail in this scenario. Transaction log-based solutions are one possible way to go here, but unfortunately I had to roll my own.
My point is this: it's unlikely that an unmodified generic solution will work for any particular database. OTOH, it's always nice to see how other people approach these problems - there's usually something to learn - even from the mistakes.
-
SprocKing says: "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."
here is an additional issue with the article.
1. Instead of triggers overright your update statement and you can't refer blob columns. That means you have no mechanism of updating the blob column through instead of triggers.
2. let em clarify on what I mentioned. If you update the blob fields using UPADTETEXT or WRITETEXT, triggers will not fire. Triggers will fire only if you update them through Update statement. (If you disagree check the example give here.) That means Audit trigger is not a reliable solution if you have ad-hoc queries and blob columns.
Here is the example.
I am using SQL Server 2000 Sp4 with all the latest hotfixes.
IF Object_ID('TriggerTest') is NOT NULL
Drop table TriggerTest
GO
Create table TriggerTest
(
IntID int identity(1,1) primary key clustered,
Field1 varchar(20) not NULL,
Field2 text NOT NULL Default(''),
Last_Modified datetime NOT NULL default(GetDate())
)
GO
Create Trigger trg_TriggerTest_Ins_Upd On TriggerTest INSTEAD of Update
As
Update TriggerTest Set
Field1 = IsNULL(I.Field1, TriggerTest.Field1),
Last_Modified =CURRENT_TIMESTAMP
From Inserted I
Where I.IntID = TriggerTest.IntID
GO
Set IDENTITY_INSERT TriggerTest ON
Insert TriggerTest (IntID, Field1) Values(1, 'Record 1')
Set IDENTITY_INSERT TriggerTest OFF
Select * from TriggerTest
WAITFOR DELAY '00:00:00.500'
Update TriggerTest Set Field2 ='Row 1' Where IntID =1
Select * from TriggerTest
WAITFOR DELAY '00:00:00.500'
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(Field2)
FROM TriggerTest
WHERE IntID =1
WRITETEXT TriggerTest.Field2 @ptrval 'The text column for row 1 is added through WRITETEXT.'
Select * from TriggerTest
WAITFOR DELAY '00:00:00.500'
Declare @LEN int
SELECT @ptrval = TEXTPTR(Field2), @Len= DataLength(Field2)
FROM TriggerTest
WHERE IntID =1
UPDATETEXT TriggerTest.Field2 @ptrval @LEN 0 ' The text column for row 1 is updated through UPDATETEXT'
Select * from TriggerTest
WAITFOR DELAY '00:00:00.500'
Update TriggerTest Set Field1 ='record1' Where IntID =1
Select * from TriggerTest
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
Preethiviraj Kulasingham
1. "Instead of triggers overright your update statement and you can't refer blob columns. That means you have no mechanism of updating the blob column through instead of triggers."
This is a bit misleading. The subject of the article is auditing via triggers, not updating the same table's blobs via triggers. Blob fields are accessible for "INSTEAD OF" auditing in the Inserted and Deleted tables.
See BOL:
"SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers."
2. This is interesting. Although it is true that UPDATETEXT/WRITETEXT (and potentially bulk copies) are not logged and do not fire triggers, these functions are not commonly used. Most blob updates are performed through regular Update statements that are logged and fire triggers. However, when UPDATETEXT/WRITETEXT statements are used, it is simple enough to manually update the audit table, e.g. in the same SP. I don't think this is a reason to reject the technique. It's just an unusual application-specific quirk that we may need to take into account when writing audit procedures.
-
Another issue nobody noticed (
):IT'S NESTED TRIGGER
INSTEAD OF UPDATE trigger makes UPDATE to the same table and fires itself.
I don't see in trigger any mean to stop this endless loop.
But in general view:
wouldn't it be easier to have separate table to log changes:
ObjectId, PK_Id, DateRecorded, UserId.
Triggers on monitored tables will just add records to this table.
Easy and reliable.
And you don't need to care about freaky datatypes (did anybody think about user defined ones?) in monitored tables.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply