Look for adjustments to an SQL Server table?

Just how can I check an SQL Server data source for adjustments to a table without making use of triggers or changing the framework of the data source at all? My recommended shows setting is .NET and also C#.

I would certainly such as to have the ability to sustain any kind of SQL Server 2000 SP4 or more recent. My application is a bolt-on information visualization for an additional firm's item. Our consumer base remains in the thousands, so I do not intend to need to place in needs that we change the third-party supplier's table at every installment.

By " adjustments to a table" I suggest adjustments to table information, not adjustments to table framework.

Inevitably, I would certainly such as the adjustment to cause an occasion in my application, as opposed to needing to look for adjustments at a period.


The most effective strategy offered my needs (no triggers or schema alteration, SQL Server 2000 and also 2005) appears to be to make use of the BINARY_CHECKSUM function in T-SQL. The means I intend to implement is this:

Every X secs run the adhering to question:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);
.

And also contrast that versus the saved value. If the value has actually transformed, experience the table row by row making use of the question:

SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);
.

And also contrast the returned checksums versus saved values.

0
2019-05-06 23:25:18
Source Share
Answers: 6

Unfortunately, I do not assume that there is a tidy means to do this in SQL2000. If you tighten your needs to SQL Server 2005 (and also later on), after that you stay in business. You can make use of the SQLDependency class in System.Data.SqlClient. See Query Notifications in SQL Server (ADO.NET).

0
2019-05-12 13:55:09
Source

Have a DTS work (or a work that is begun by a windows solution) that goes for an offered period. Each time it is run, it obtains details concerning the offered table by utilizing the system INFORMATION_SCHEMA tables, and also documents this information in the information database. Contrast the information returned pertaining to the framework of the table with the information returned the previous time. If it is various, after that you recognize that the framework has actually transformed.

Instance question to return details pertaining to every one of the columns in table ABC (preferably detailing out simply the columns from the INFORMATION_SCHEMA table that you desire, as opposed to making use of *select * * like I do below) :

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'ABC'

You would certainly check various columns and also INFORMATION_SCHEMA sights relying on just how specifically you specify "adjustments to a table".

0
2019-05-08 20:43:22
Source

Why do not you intend to make use of triggers? They are a good idea if you utilize them appropriately. If you utilize them as a means to apply referential honesty that is when they go from excellent to negative. Yet if you utilize them for surveillance, they are not actually taken into consideration taboo.

0
2019-05-08 19:48:20
Source

How usually do you require to look for adjustments and also just how huge (in regards to row dimension) are the tables in the data source? If you make use of the CHECKSUM_AGG(BINARY_CHECKSUM(*)) method recommended by John, it will certainly check every row of the defined table. The NOLOCK tip aids, yet on a huge data source, you are still striking every row. You will certainly additionally require to store the checksum for every single row to make sure that you inform one has actually transformed.

Have you taken into consideration addressing this from a various angle? If you do not intend to change the schema to add triggers, (that makes a feeling, it's not your data source), have you taken into consideration collaborating with the application supplier that does make the data source?

They can implement an API that gives a device for alerting accessory applications that information has actually transformed. Maybe as straightforward as contacting an alert table that details what table and also which row were changed. That can be applied via triggers or application code. From your side, ti would not matter, your only problem would certainly be checking the alert table on a routine basis. The efficiency appealed the data source would certainly be much much less than scanning every row for adjustments.

The tough component would certainly be encouraging the application supplier to implement this attribute. Given that this can be takes care of totally via SQL using triggers, you can do the mass of the benefit them by creating and also examining the triggers and afterwards bringing the code to the application supplier. By having the supplier sustain the triggers, it protect against the scenario where your including a trigger accidentally changes a trigger provided by the supplier.

0
2019-05-08 14:40:12
Source

Wild hunch below : If you do not intend to change the 3rd party's tables, Can you create a sight and afterwards placed a trigger on that particular sight?

0
2019-05-07 18:12:40
Source

Take an appearance at the CHECKSUM command :

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);
.

That will certainly return the very same number each time it's run as long as the table materials have not transformed. See my blog post on this for additional information :

CHECKSUM

Here's just how I utilized it to restore cache dependences when tables transformed :
ASP.NET 1.1 database cache dependency (without triggers)

0
2019-05-07 17:13:49
Source