I have a perl script (uses sql query) that will run if there is any
update in the row. Currently, I am a trigger that sets the DateModified
field with getdate() Then my perl script looks for certain range of
modified dates and runs the necessary query.

I am trying to come up with a different mechanism where I don't have to
use the date field. I am looking into flag bits. But not sure how
to use it.

Should I create a trigger that will set the flag bit after any update?
This way the script will just look for the updated records, regardless
of what time it was updated. Maybe I am misunderstanding flag bits, then
after another subsequent update how would the perl/sql script know which
records were updated? I hope this make sense.

thanks.

--s

Sharif Islam (mislam@spam.uiuc.edu) writes:
> I have a perl script (uses sql query) that will run if there is any
> update in the row. Currently, I am a trigger that sets the DateModified
> field with getdate() Then my perl script looks for certain range of
> modified dates and runs the necessary query.
>
> I am trying to come up with a different mechanism where I don't have to
> use the date field. I am looking into flag bits. But not sure how
> to use it.
>
> Should I create a trigger that will set the flag bit after any update?
> This way the script will just look for the updated records, regardless
> of what time it was updated. Maybe I am misunderstanding flag bits, then
> after another subsequent update how would the perl/sql script know which
> records were updated? I hope this make sense.

There are a couple of alternatives. Which version of SQL Server are you
using? If you use SQL 2005, Query Notification is an interesting
alternative. Your Perl script would issue a query, and then wait until
the result set changes. Of course, this presumes that you use an API
that supports Query Notification, but Win32::SqlServer, available from
my web site on http://www.sommarskog.se/mssqlperl/index.html does.

Another alternative is to use a timestamp column. A timestamp column
in SQL Server has nothing do with date and time, but is automatically
updated each time a row is updated with a database-unique value that
grows monotonically. Thus, the Perl script could look at the timestamp
column and save the latest value as a high-water mark. This would at
least save you the trigger.

I don't believe in flag bits. In this case, the Perl script would have
to flip them back, and that's more complex.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog wrote:
> Sharif Islam (mislam@spam.uiuc.edu) writes:
>> I have a perl script (uses sql query) that will run if there is any
>> update in the row. Currently, I am a trigger that sets the DateModified
>> field with getdate() Then my perl script looks for certain range of
>> modified dates and runs the necessary query.
>>
>> I am trying to come up with a different mechanism where I don't have to
>> use the date field. I am looking into flag bits. But not sure how
>> to use it.
>>
>> Should I create a trigger that will set the flag bit after any update?
>> This way the script will just look for the updated records, regardless
>> of what time it was updated. Maybe I am misunderstanding flag bits, then
>> after another subsequent update how would the perl/sql script know which
>> records were updated? I hope this make sense.
[...]
>
> Another alternative is to use a timestamp column. A timestamp column
> in SQL Server has nothing do with date and time, but is automatically
> updated each time a row is updated with a database-unique value that
> grows monotonically. Thus, the Perl script could look at the timestamp
> column and save the latest value as a high-water mark. This would at
> least save you the trigger.

Thanks, the timestamp solution seems feasible. I am little confused on
how to use it.

According to the documentation SELECT @@DBTS should return the records
that were just updated or modified. After some updated, when I ran the
select statement, I am getting all the rows. Is there anything else I
need to do? When I created the field I just assigned the data type,
didn't include any default vaule (using MSSQL 2005).

--s

>> I am trying to come up with a different mechanism where I don't have to use the date field [sic: columns are not fields]. I am looking into flag bits. <<

All that will do is destroy data you have collected. Just use the
date.

Sharif Islam (mislam@spam.uiuc.edu) writes:
> Thanks, the timestamp solution seems feasible. I am little confused on
> how to use it.
>
> According to the documentation SELECT @@DBTS should return the records
> that were just updated or modified. After some updated, when I ran the
> select statement, I am getting all the rows. Is there anything else I
> need to do? When I created the field I just assigned the data type,
> didn't include any default vaule (using MSSQL 2005).

Not sure what you mean here, as SELECT @@DBTS returns a single value.
Probably you ran some other query, but as I don't see it, I can't say
what's wrong with it. :-)

Rather than using @@DBTS, min_active_rowversion() is a better choice.
This function was added in SP2, and it was added to Books Online as late
in the September 2007 edition (see my signature for download link). Looking
at @@dbts can cause some issues when there are uncommitted transaction.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog wrote:
> Sharif Islam (mislam@spam.uiuc.edu) writes:
>> Thanks for the . Here's how I am using it. I created a column 'Flag'
>> with timestamp datatype.
>>
>> ------
>>
>> declare @before timestamp
>> declare @after timestamp
>> set @before= min_active_rowversion() -1
>> update MyTable set MyCol ='Test' where MyCol like 'Test123%'
>> set @after = min_active_rowversion() -1
>>
>> select ID,MyCol from MyCol where Flag -1 < @after
>> and Flag -1 >= @before
>> -----
>> This gave me the list of record ID that was just changed. Is this the
>> way to use min_active_rowversion()?
>
> Hm, not really.
>
> Your Perl script would run a batch like:
>
> DECLARE @new_highwater_mark rowversion
> SELECT @new_highwater_mark = min_active_rowversion()
>
> SELECT ID, MyCol, @new_highwater_mark
> FROM tbl
> WHERE tstamp >= @last_highwater_mark AND
> tstamp < @new_highwater_mark
>
> That is, the Perl script needs to remember @new_highwater_mark, and pass
> it as @last_highwater_mark next time.
>
> It's important to capture min_active_rowversion() into a variable, because
> it could change while the query is running, which could lead to lost
> updates.
>
>
Got it! Thanks a lot.