March 12, 2009 at 6:04 pm
I am writing a C# trigger to manage history auditing. Essentially I am logging any change to any column of tables in the database. I have a unique identitifier in all of the tables that I want to add to the audit table to allow me to tie back to the data table.
I create an instance of DataTable and fill this with the contents of the insert buffer. I loop through each row analyzing each column for changed values. But I have the logic:
if (column.AutoIncrement)
uniqueID = int.Parse(row[column].ToString();
But this AutoIncrement check never detects the identity column.
The trigger is properly logging column names, values and the like but I need to identify the indentity column to save this as the row key.
I am building this trigger in Visual Studio 2005. The database is SQL Server 2005 version 9.00.xxx
Any help would be appreciated.
Kevin McGinn
March 12, 2009 at 8:31 pm
I don't think that the inserted table columns do not have the Identity property. I would recommend that you check the is_identity property of the sys.columns table instead. Or just use the SCOPE_IDENTITY() SQL function.
The real question of course is why you would use C# to write a trigger in the fist place, since this would be significantly easier (and probably faster) in SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2009 at 8:35 am
Using the system tables was my fall back; I was just of the impression that there would be a better method of determining column attributes.
As to why I took this approach; it a matter of economy of scale. I have 12 or more tables that I need to track all actions taken on all columns of the tables. Instead of writing trigger code for all of the tables I write a single assembly that is used by all tables.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply