Help with Trigger

[ermora]

What I'd like to do is create a trigger that will be used whan an UPDATE transactions is performed against a table. What I want this trigger to capture is as follows;

-RecordID (auto)
-Date/Time
-User ID
-Table Name
-Name Of Field Changed
-Old Field Value
-New Field Value

If you could help, I'd appreciate it.

Thanks

 

[druer]

I'm assuming you can follow the Books On Line in terms of how to create an Update Trigger so I won't elaborate on that.

1. Understand that doing this will add a significant time to any update transaction.

2. The key thing to determine Old/New field values involves utilizing the "magic" tables Inserted and Deleted inside of the trigger.

3. The following assumes that your trigger will only deal with a single update at a time as a way to simply illustrate what would need to be done. It will NOT work if you issue commands like Update table where city = LA or something that updates multiple rows. However, I think you'll be able to make modifications as necessary.

4. In terms of the Trigger an update is really the combination of a Delete of the old value(s), and an insert for the new value(s), thus you have to compare the values from the DELETED table and the INSERTED table.

if Deleted.FieldName <> Inserted.FieldName
begin
-- insert into audittable values (...., deleted.fieldname, inserted.fieldname)
end

(You can do things like declaring variables for the fields, and then selecting all of the old values in 1 swoop, and all of the new values in one swoop and then compare, and use them so you only hit the Inserted/Deleted tables one time.)

Declare @OldField1 Datatype
Declare @NewField1 Datatype
Declare @OldField2 Datatype
Declare @NewField2 Datatype

select @OldField1 = Field1, @OldField2 = Field2 from Deleted
select @NewField1 = Field1, @NewField2 = Field2 from Inserted

if (@OldField1 <> @NewField1) ...
insert into .... (.... , @OldField1, @OldField2)

5. Again the time taken to lookup the values in the INSERTED and DELETED tables is a time consuming operation, and doing it your way would require a great deal of time to do field by field.

6. An alternative (which would perform better) is laid out in Books on Line and involves having a separate audit table for each table you want to audit and it simply writes all of the fields that you care about at the same time. In other words, 1 audit written for all of the old values, and 1 writtern for all of the new values. This approach handles all of the inserts if there are multiples.

 

[SQLDenis]

just as a caution
if (@OldField1 <> @NewField1) this will fail if the field was NULL and is updated to something else or vice versa
Some fun with update triggers

http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

I forgot about the NULL nonsense. In that case then a simple ISNULL won't work either

ISNULL(@OldField1, '') <> ISNULL(@NewField1, '')

Would yield false results. Assume that I'm setting the field to '' instead of leaving as it was NULL. The comparison would yield that the fields do match, however, they really don't as one was NULL and the other was blank ''.

The only thing I could come up with handle OLD/NEW Nulls is:
if (@var1 <> @var2) or (@var1 IS NULL and not @var2 IS NULL) or (NOT @var1 IS NULL and @Var2 is NULL)

 

[ermora]

Thanks for the input

I was looking to have a trigger that would dynamically identify the Field Name, Old Value & New Value and save this information into a single row (aside of user information). This would mean that a single update could produce X number of new rows in the audit table.

I understand that performance would drop, is it -or- would it be that substantial? Would it make more sense to just copy the row before the update (into another table)?

Thanks

 

[druer]

Unfortunately (to my knowledge anyway) there would be no way to do this dynamically because the Inserted and Deleted tables are only available within the triggers execution context. So if you try to create a command string and use "EXEC" or something, you are out of luck in accessing the data.

Performance is always relative to the existing workload and quality of hardware etc.

One example I'll share is with a trigger (audit) that was in place from a third party solution. It simply did a check like I indicated and then wrote to the audit. A full 90% of the time in executing the update for the table, and then doing the trigger to insert to the audit table was actually involved in reading the data from the Inserted/Deleted tables. The problem was not so much in the time itself, as in the fact that we have about 1000 commands per minute that invoked this trigger, thus wasting the 90% of time. It turned out that the audit was only there in case we wanted (per the 3rd party.) Since removing that particular audit we got rid of abou 1200 blocking cases per day.

The solution in books on line would give you the ability to audit everything with a limited amount of impact. You can create a SQL Script that would dynamically create the trigger for you, and the audit table as well, and then simply PRINT the command that you've created dynamically. Take the script and then execute it.

 

[jbenson001]

If you are looking for what columns have been Updated, you can check this quote from BOL. See if it helps you:

IF UPDATE (column)

Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.



Note The IF UPDATE (column) clause functions identically to an IF, IF...ELSE or WHILE statement and can use the BEGIN...END block. For more information, see Control-of-Flow Language.


UPDATE(column) can be used anywhere inside the body of the trigger.

column

Is the name of the column to test for either an INSERT or UPDATE action. This column can be of any data type supported by SQL Server. However, computed columns cannot be used in this context. For more information, see Data Types.

IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.

The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger.

Jim

 

[SQLDenis]

IF UPDATE (column) will fire even if you do something like this

update table set value = 100 where value = 100

you have to test inserted against deleted tables and take NULLS into acccount

Some fun with update triggers

http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/