July 25, 2008 at 9:20 am
I have run into a big problem with our "generic" audit trigger. The generic table where we insert the insert/update/deleted values don't contain information where we will be able to figure out to which record the change was made to. We have triggers for 9 different tables, and insert the insert/update/delete into one commmon table:
id (sequence number)
batch_id (identifies a set of related changes; i.e. multiple values were modified in one record with the same action)
table (modified)
column (modified)
original_value
new_value
user_id
timestamp
The only thing we can come up with is to have a seperate table containing the batch_id, column_name and value where we will insert the batch_id, name of the primary/foreign key column name, and the actual primary/foreign key value. Then create a select that will use the batch_id to figure out which changes has been made. This is issue seems like it is blowing way out of proportion for a non-sql person like me.
So, is there a way in T-SQL where I can fetch all the primary keys, foregin keys?
The problem is that some of the tables contain more than one primary key, and some tables have more than one primary key and foreign keys.
Any ideas?
July 25, 2008 at 10:00 am
Take a look at these two articles:
http://qa.sqlservercentral.com/articles/Auditing/63247/
http://qa.sqlservercentral.com/articles/Auditing/63248/
And this discussion thread:
http://qa.sqlservercentral.com/Forums/Topic536927-145-2.aspx
There's a lot of data about auditing and triggers in those. You should be able to come up with something that will do what you need, based on them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 12:24 pm
The problem I have with some of these solutions is that they save it as xml. We need to be able to create reports later on in Crystal or SSRS to fetch the changes made to some of our tables. How would I do this if the changed data is saved as xml in one column?
July 25, 2008 at 12:29 pm
You can have a proc query the XML data and turn it back into relational data, if you like. Use Nodes() and Value() functions to turn it back into relational tables.
On the other hand, I'm pretty sure Crystal can deal with XML data sources. I could be wrong, since I haven't used it in a few years, but it should be able to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 27, 2008 at 12:20 am
Hope the below article may be useful for you to achieve your task,
http://venkattechnicalblog.blogspot.com/2008/07/to-fetch-all-constraints-in-sql-server.html
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
July 28, 2008 at 12:52 pm
The following query will give you all of the Primary Key columns for a table:
SELECT
PK.TABLE_NAME AS Tbl, PK.CONSTRAINT_NAME AS PKName
, KC.COLUMN_NAME AS Col, KC.ORDINAL_POSITION AS OrdPos
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC ON
PK.TABLE_NAME = KC.TABLE_NAME
AND PK.CONSTRAINT_NAME = KC.CONSTRAINT_NAME
WHERE
PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND PK.TABLE_NAME = 'Your Table Name'
The following query will give you the Foreign Key columns (the children) in a particular table:
SELECT C.TABLE_NAME, V.COLUMN_NAME, C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE V ON
C.TABLE_NAME = V.TABLE_NAME
AND C.CONSTRAINT_NAME = V.CONSTRAINT_NAME
WHERE
C.TABLE_NAME = 'Your Table Name'
AND C.CONSTRAINT_TYPE = 'FOREIGN KEY'
Hope this helps!
Todd Fifield
July 28, 2008 at 1:15 pm
hengert (7/25/2008)
The problem is that some of the tables contain more than one primary key, and some tables have more than one primary key and foreign keys.
Not to be nitpicky here (but some will say i am), you can only have one primary key on a table. You can have multiple unique indexes on different columns, but only one primary key.
😎
July 28, 2008 at 1:22 pm
Yes, you are correct.
This is what I have and it seems to work like I want it too.
Thanks.
DECLARE c_keys CURSOR FOR
SELECT DISTINCT
key_column = CCU.column_name
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON
CCU.COLUMN_NAME = KCU.COLUMN_NAME AND
CCU.TABLE_NAME = KCU.TABLE_NAME AND
CCU.TABLE_SCHEMA = KCU.TABLE_SCHEMA AND
CCU.TABLE_CATALOG = KCU.TABLE_CATALOG AND
CCU.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
KCU.TABLE_NAME = TC.TABLE_NAME AND
KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND
KCU.TABLE_CATALOG = TC.TABLE_CATALOG AND
KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
(TC.CONSTRAINT_TYPE = 'PRIMARY KEY' OR TC.CONSTRAINT_TYPE = 'FOREIGN KEY') AND
CCU.Table_Name = @TableName
OPEN c_keys
FETCH Next FROM c_keys INTO
@KeyColumn
SET @KeyValueStatement = ''
WHILE @@Fetch_Status = 0
BEGIN
END
July 28, 2008 at 2:01 pm
Not to be picky, but a single primary key can contain more than 1 column.
Todd Fifield
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply