Dirty Bit! - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dirty Bit!

Author

Topic

OwenW
Starting Member

11 Posts

Posted - 2006-11-30 : 15:56:01

I need a procedure that updates a field in any record to indicate that some of the data in the record has been changed. DB2 uses a 'dirty bit' to show that the record was altered, and that bit can be queried easily update logging. What is the simplest way to accomplish this in SQL?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 16:06:24

Add a new column named DirtyBit to your table.
Use a trigger to update this column for every row updated.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 16:12:53

If you add a rowversion (previously known as a timestamp in SQL Server) column to your table, its value will change whenever the row is updated. It's obviously not quite the same because it assumes that you record the current value of that column at the beginning of soem operation and then check to see if it has changed later, but if you can do that it will let you easily detect changed rows.
The values are unique within each database and are sequential although you should infer no meaning from the number other than that if a row's rowversion is different from what it was then something in the row changed, and if a row's rowversion is higher than some other row's rowversion then it was changed more recently than the other row.

OwenW
Starting Member

11 Posts

Posted - 2006-11-30 : 16:13:13

How does the trigger know which records have updated data? As far as I can tell, the trigger would update all the records in the table if it activates.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-30 : 16:18:09

look into COLUMNS_UPDATED() function

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

OwenW
Starting Member

11 Posts

Posted - 2006-11-30 : 16:24:06

SQL help does not reference that function. Where can I look for information?

OwenW
Starting Member

11 Posts

Posted - 2006-11-30 : 16:29:10

Peso,

Could you illuminate me on how you code a trigger to set a bit to true for only records that have had some data changed?

My problem is I have an application that uses a SQL database to store drawing data, but it does not have any logging to show what data it changed and when. I am trying to create that logging in a format that I can access and use. The transaction log is not usable for this purpose, as far as I know.

Thanks for all the input!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-30 : 16:29:44

are you using sql server?

there is info in BOL = books online = sql server help there

you can look here for use:
http://www.databasejournal.com/features/mssql/article.php/1479821

and description:
http://msdn2.microsoft.com/en-us/library/ms186329.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

OwenW
Starting Member

11 Posts

Posted - 2006-11-30 : 16:43:19

COLUMNS_UPDATED() looks handy. Aside from the fact that it looks for actions that affect multiple rows (which I am not) and it is only available for SQL Server 2005 (which I do not have

). We are still running MSSQL 2000. I do appreciate the input, though!

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 16:52:20

"As far as I can tell, the trigger would update all the records in the table if it activates."

Wouldn't be much use if it did!

Have a look in Books Online for the INSERTED pseudo-table available to triggers which contains the rows that have changed.

"that it looks for actions that affect multiple rows (which I am not)"

Make sure you build your trigger to handle multiple rows in a single update - even if you are not currently doing that. Otherwise you will be heading for a fall at some future point!

Kristen

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-11-30 : 16:56:32

You may want to do some searching on "audit logging" or "audit trail." There are a few different ways to accomplish these tasks besides adding an extra field to every table.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 17:00:48

"to indicate that some of the data in the record has been changed"

"You may want to do some searching on "audit logging" or "audit trail.""

Good point. Seems to be not very helpful to only know that the data has changed from the original, rather than (say) that it has changed yet again, or changed since the 99-Xxx-9999.

For Audit Triggers see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Trigger,Triggers,audit%20changes

Kristen

OwenW
Starting Member

11 Posts

Posted - 2006-12-01 : 14:13:35

OK, I have working trigger, but I do have a question about the update query. I was unable to reference the 'inserted' table in the main query. Instead, I had to put the reference to the 'inserted' table in a subquery. Any ideas why?

USE PMR
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ('tr_SetModifiedFlags', 'TR') IS NOT NULL
DROP TRIGGER tr_SetModifiedFlags
go
CREATE TRIGGER tr_SetModifiedFlags
ON PID_Components_Valves
AFTER INSERT,UPDATE
AS
IF COLUMNS_UPDATED() > 0
BEGIN
SET NOCOUNT ON;
UPDATE PID_Components_Valves
SET WHEN_MODIFIED_ = getdate() , WHO_MODIFIED_ = user
WHERE ID_COUNT_ IN
(
SELECT ID_COUNT_ FROM inserted I where I.ID_COUNT_ = PID_Components_Valves.ID_COUNT_
)
END
GO

Thanks for all the help!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-01 : 14:57:29

If you want to know the error in a query that isn't working, you'll need to post it.

Jay White

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-01 : 15:21:18

columns_updated is available in sql server 2000.
if you'd read the first link i posted you'd see that.
here's another one though:
http://www.sql-server-performance.com/trigger_tuning.asp

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources