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 |
|
SubCreator
Starting Member
8 Posts |
 Posted - 2005-01-25 : 05:13:04
|
| I have a vague memory of reading somewhere that it's possible to see what's been changed by a UPDATE.I know the UPDATE and AFTER UPDATE triggers.I have a student database where I log all changes in the personalinfo via an asp-page. I would like to make a function that retrieves the changes only. Does SQL has anything to offer to help me out?I run MS SQL Server 2000.-- SubCreator |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-25 : 06:18:39
|
well if you have an after update triggr that inserts changes to another table then you can look them up there.otherwise you'll need to look at transaction logs if you have them...Go with the flow & have fun! Else fight the flow  |
 |
|
|
sreemace
Starting Member
9 Posts |
Posted - 2005-01-25 : 06:49:45
|
| Does it get in Transaction log??????? As its a simple Update or Insert, how can that be transaction oriented........Thanks & Regards,Sreejith G |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-25 : 06:52:10
|
| everything sql server does is basicaly a transaction in itself. so everything gets saved in a transaction log, if that's how you got it set up.Go with the flow & have fun! Else fight the flow |
|
|
|
sreemace
Starting Member
9 Posts |
Posted - 2005-01-25 : 06:59:18
|
| Then why do we have SET IMPLICIT_TRANSACTION ON & EXPLICIT_TRANSACTION stuff.... I experimented it and I could see in query analyser its default 'EXPLICIT', If we do not specify 'SET IMPLICIT_TRANSACTION ON' it wont consider as a Transaction...... How can we see transaction log entries???Thanks & Regards,Sreejith G |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-01-25 : 07:40:36
|
As spirit1 said (dependant on set up) everything is saved in a transaction log.From BOLquote:
Autocommit transactionsThis is the default mode for SQL Server. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.
Also from BOL, even TRUNCATE TABLE is recorded in the transaction logquote:
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
You cant read what is in the transaction log, but there are software packages out there which do enable you to do so. I've never used any of them so i cant comment.I cant remember what the software is called but someone on here will definitely knowAndy |
|
|
|
SubCreator
Starting Member
8 Posts |
Posted - 2005-01-25 : 09:53:08
|
quote:
Originally posted by spirit1
well if you have an after update triggr that inserts changes to another table then you can look them up there.otherwise you'll need to look at transaction logs if you have them...
OK. What I am looking for, is how to insert the changes into that OTHER table :)Is it like SELECT * FROM CHANGES ...or something?-- SubCreator |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-25 : 10:08:57
|
| there are 2 pseudo tables in After update trigger. - table "deleted" that contains the old values - table "inserted" that contains the new valuesso you doinsert into OtherTable (columns)select columnsfrom insertedalso check out the CREATE TRIGGER in BOLGo with the flow & have fun! Else fight the flow |
|
|
|
|
|
|
Retrive the changes from an update