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