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 |
|
maevr
Posting Yak Master
169 Posts |
 Posted - 2008-08-21 : 02:09:07
|
| How do I write to get a trigger to record all column changes to a database.I have a table Audit_changescreate table #audit_changes( id int identity(1,1) not null, regtime datetime not null, username varchar(20) not null, tableName varchar(50) not null, columnName varchar(50) not null, oldValue varchar(max) not null, newValue varchar(max) not null primary key (id))I want the trigger to insert the time, username of the logged in user and the oldValue and the newValue into the above table for the entire database.In my database I have approx. 100 tables with alot of columns in all of them, therefore I want it to fetch the name of the changed table and column. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-21 : 03:56:11
|
| i dont think this is a good approach. you need to have triggers on all your tables to make this possible. whats the actual reason behind logging all changes to your db? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-21 : 17:20:04
|
| you can see schema change report for it and who did it. |
|
|
|
maevr
Posting Yak Master
169 Posts |
Posted - 2008-08-22 : 01:31:08
|
| The reason to this is that the data in the database is not ok, many values need to be changed and this will be done through an ODBC connection to MS Access that will be used by two users that is not familiar to SQL or databases.We need to know who changed the value, what time and the old/new value to be able to change back in case of errors.The database that the users is going to change in is not the orginal one it is a copy that Cognos BI 8 will run against.Have anyone else got an idea how to do this. |
|
|
|
|
|
|
Update Trigger on database level.