Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2005-01-25 : 12:34:03
|
| I'm trying to setup an audit trail to track data changes in a specific table. The table structure is already setup and can't be changed. In addition there are no sprocs to alter/insert data so I am setting up some triggers.My problem is that I want to audit the contents of some fields that are of type TEXT and my trigger can't cope.I'm using SQL Server 7. (so I only have insert, update and delete triggers)If anyone has any ideas - brilliant or otherwise - I would be gratefulmany thankssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-25 : 12:37:00
|
well you could add a job that addes new text field data into an audit table...Go with the flow & have fun! Else fight the flow  |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-26 : 03:32:05
|
| Yeah, that would work - cheers spiritsteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-26 : 08:21:20
|
| Without wishing to sound difficult (why do people always say that when they are about to be difficult!!) I've realised that my requirement is to track ALL changes to these text fields, so I am thinking along the lines of creating a stored procedure that does what you suggested and calling that from triggers. I presume that you can't have multiple statements in a trigger in the same way that you might in a stored procedure. The problem then is joining it all back together if I want to make sense of it, how will I know what in the (non Text) audit table relates to what in the Text audit table.Any ideas?thanks againsteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-26 : 10:20:45
|
| you can have multiple statemnts in a trigger. trigger is a kind of s stored procedure.well put the connecting id in both audit tables. if you have a emplyees tableput emplyeeId in both of them.Go with the flow & have fun! Else fight the flow |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-26 : 10:37:11
|
| Multiple statements will sort it - thanks again SpiritsteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-26 : 14:26:01
|
Don't forget that the trigger will be given ALL the rows in an update, not just one row.You should be able to get the TEXT from the actual (JOINed) table - although I suppose it might have already been changed by someone else.INSERT INTO MyAuditTable (MyPK, SomeOtherInt)SELECT ThePK, TheOtherIntFROM inserted IUPDATE USET MyAuditTextColumn -- Just update the TEXT columnFROM MyAuditTable U JOIN inserted I ON U.MyPK = I.ThePK Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-27 : 04:08:15
|
| KristenMany thanks for that - we have a relatively low number of users and these fields are the sort that are unlikely to be subject to change by more than one person at a time for any given record so what you've provided should be just the jobcheerssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
|
|
|
|
|
This little piggy...