Visual Basic code to write the last time a Table was updated in a DB

Question

Visual Basic code to write the last time a Table was updated in a DB

OP_HELP

SSCrazy

Points: 2618
More actions
October 22, 2008 at 11:31 am

#194882

I want to show the last Time and Date that a Table in a Database was updated. I'm writing this in Visual Basic, using an ASP page in Visual Web Express. I can't find this solution anywhere for some reason. Does anyone know how to do this? thanks for your help.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Unless you have a datetime column on the table that holds the last updated date, you won't be able to. There is no internal mechanism that tracks it for you.

OP_HELP SSCrazy Points: 2618 More actions · Answer 2

Are you saying that a column can be included in the table which is automatically populated with the last time the table was updated? If so, that would work for what I'm trying to accomplish. How do you implement such a feature?

If this is the case, it would be even more better to show the data for when the last time the row was updated.

Once again, thanks for any help.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

umanpowered (10/23/2008)
Are you saying that a column can be included in the table which is automatically populated with the last time the table was updated? If so, that would work for what I'm trying to accomplish. How do you implement such a feature?

The above is really quite simple. You add a datetime column called lastupdated to the table, and set its value to getdate() in any code that changes data on the table. Alternately, you can create a after insert, update trigger on the table that sets the value of this column to getdate() after each insert or update. If you can do it without the trigger then I would recommend it.

If this is the case, it would be even more better to show the data for when the last time the row was updated.
Once again, thanks for any help.

This actually is a bit more indepth and requires a revision tracking table for each table that you want to monitor. The following should do what you need, but I will provide a warning.

This uses dynamic SQL to build tables and triggers in your database. Before running this test it on a non-production machine. I am not responsible for any damage or downtime incurred from the use of the below code. Use it at your own risk.

SET NOCOUNT ON

DECLARE @tab sysname,

@sql VARCHAR(2000),

@trigger NVARCHAR(255),

@baseTab NVARCHAR(255),

@IdentityName sysname,

@columns varchar(2000)

DECLARE @table TABLE (tab_name VARCHAR(2000))

--Get table names

INSERT INTO @table

select o.name

from sys.objects o

join sys.schemas s on s.schema_id = o.schema_id

where o.type = 'u'

and o.name in ('TableName')

and s.name = 'dbo'

order by o.name

SELECT @tab = min(tab_name)

FROM @table

WHILE @tab IS NOT NULL

BEGIN

BEGIN TRANSACTION

SET @sql = NULL

SET @baseTab = 'REV_' + @tab

SET @sql = 'IF EXISTS (SELECT name FROM sysobjects ' + CHAR(13) + CHAR(10) +

'WHERE name = ''' + @basetab + ''' AND type = ''U'')' + CHAR(13) + CHAR(10) +

'DROP TABLE ' + @basetab + CHAR(13) + CHAR(10) +

'GO'

-- EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

set @columns = 'IDENTITY(INT,0,1) AS [RowID], convert(varchar(50), null) [Action], convert(datetime, null) [ChangeDate], '

select @columns = @columns + case WHEN is_identity = 1 then 'CONVERT(int, ' + name + ') as [' +name + ']' else name end + ', '

from sys.columns

where object_id = object_id(@tab)

select @columns = left(@columns, len(@columns) -1)

-- Create revision table

SET @sql = 'SELECT '+@columns +char(10) +

'INTO ' + 'REV_' + @tab + ' ' + char(10)+

'FROM ' + @tab + ' ' + char(10) +

'WHERE NOT EXISTS (SELECT 1)' + char(10) +

'GO'

-- EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

COMMIT

BEGIN TRANSACTION

SET @IdentityName = NULL

SELECT @IdentityName = c.name

FROM sys.columns c

JOIN sys.objects o ON o.object_id = c.object_id

WHERE o.name = @tab

AND is_identity = 1

PRINT '-- Create a Clustered Index '

IF @IdentityName IS NOT NULL

BEGIN

SET @sql = 'ALTER TABLE REV_'+@tab+ ' ADD CONSTRAINT PK_REV_'+@tab +' PRIMARY KEY CLUSTERED ( RowID )' + char(10) +

'GO'

--EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

SET @sql = 'CREATE NONCLUSTERED INDEX IX_REV_' +@tab + ' ON REV_'+@tab+' ('+@IdentityName+')' + char(10) +

'GO'

--EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

END

COMMIT

BEGIN TRANSACTION

-- Create Revision trigger for each base table

SET @trigger = 'REVTRIG_' + @tab

SET @baseTab = 'REV_' + @tab

SET @sql = 'IF EXISTS (SELECT name FROM sysobjects ' + CHAR(13) + CHAR(10) +

'WHERE name = ''' + @trigger + ''' AND type = ''TR'')' + CHAR(13) + CHAR(10) +

'DROP TRIGGER ' + @trigger + CHAR(13) + CHAR(10) +

'GO'

-- EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

SET @sql = 'CREATE TRIGGER ' + @trigger + CHAR(13) + CHAR(10) +

'ON ' + @tab + CHAR(13) + CHAR(10) +

'AFTER INSERT, UPDATE' + CHAR(13) + CHAR(10) +

'AS DECLARE @ins INTEGER, @del INTEGER' + CHAR(13) + CHAR(10) +

'SELECT @ins = COUNT(*) FROM INSERTED' + CHAR(13) + CHAR(10) +

'SELECT @del = COUNT(*) FROM DELETED' + CHAR(13) + CHAR(10) +

'IF @ins > 0 AND @del > 0 --RECORD WAS UPDATED' + CHAR(13) + CHAR(10) +

'BEGIN' + CHAR(13) + CHAR(10) +

'INSERT INTO ' + @baseTab + CHAR(13) + CHAR(10) +

'SELECT ''UPDATED_TO'', GETDATE(), * FROM INSERTED' + CHAR(13) + CHAR(10) +

'INSERT INTO ' + @baseTab + CHAR(13) + CHAR(10) +

'SELECT ''UPDATED_FROM'', GETDATE(), * FROM DELETED' + CHAR(13) + CHAR(10) +

'END' + CHAR(13) + CHAR(10) +

'ELSE --RECORD WAS INSERTED' + CHAR(13) + CHAR(10) +

'BEGIN' + CHAR(13) + CHAR(10) +

'INSERT INTO ' + @baseTab + CHAR(13) + CHAR(10) +

'SELECT ''INSERTED'', GETDATE(), * FROM INSERTED' + CHAR(13) + CHAR(10) +

'END' + CHAR(13) + CHAR(10)+

'GO'

-- EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

SET @trigger = NULL

SET @trigger = 'REVTRIG_DEL_' + @tab

SET @sql = 'IF EXISTS (SELECT name FROM sysobjects ' + CHAR(13) + CHAR(10) +

'WHERE name = ''' + @trigger + ''' AND type = ''TR'')' + CHAR(13) + CHAR(10) +

'DROP TRIGGER ' + @trigger + CHAR(13) + CHAR(10) +

'GO'

-- EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

SET @sql = 'CREATE TRIGGER ' + @trigger + CHAR(13) + CHAR(10) +

'ON ' + @tab + CHAR(13) + CHAR(10) +

'FOR DELETE' + CHAR(13) + CHAR(10) +

'AS DECLARE @del INTEGER' + CHAR(13) + CHAR(10) +

'SELECT @del = COUNT(*) FROM DELETED' + CHAR(13) + CHAR(10) +

'IF @del > 0 --RECORD WAS DELETED' + CHAR(13) + CHAR(10) +

'BEGIN' + CHAR(13) + CHAR(10) +

'INSERT INTO ' + @baseTab + CHAR(13) + CHAR(10) +

'SELECT ''DELETED'', GETDATE(), * FROM DELETED' + CHAR(13) + CHAR(10) +

'END' + CHAR(13) + char(10) +

'GO'

-- EXEC (@sql)

PRINT @sql

PRINT ' '

SET @sql = NULL

SET @trigger = NULL

SET @baseTab = NULL

COMMIT

SELECT @tab = min(tab_name)

FROM @table

WHERE tab_name > @tab

END

Having provided my disclaimer above, I will tell you that I use the above script fairly regularly for building auditing tables and triggers where I need to track the changes to the data in a database table. You do need to understand the impact it may or may not have on your applications before rolling it into production use.

For example, if your applications rely on identity values returned from row insertions. If you misused @@IDENTITY to return this value, the application will break, because @@IDENTITY returns the last identity insert which will be based on the trigger executions into the revision tables. You should have used SCOPE_IDENTITY() to get the identity value back, and the only way to get the base table identity value back is to change the TSQL code to return the SCOPE_IDENTITY() value back to the app. Twice I have had applications break in testing because a developer made the above mistake and was using TSQL embedded in a table adapter in the application rather than a stored procedure (part of the reason I removed all table level access from production systems).

OP_HELP SSCrazy Points: 2618 More actions · Answer 4

Jonathan,

Thanks very much for the detailed row-update solution. I have to go through it and ascertain if I can implement it for my purposes. (Still a newbie...).

I implemented the getdate() function successfully. But rather than have such data appear on every single row in the table, is there some code I can use to have the updated table trigger the creation of another table (like a one-row table) which shows the Date Time?

I'd rather use such a one-row/datetime solution because I can easily "Objectify" its content on the ASP pages I write .

Once again, thanks for your help.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

I don't understand what you are asking in your last question. Can you provide an example of what you are thinking?

OP_HELP SSCrazy Points: 2618 More actions · Answer 6

Thanks for replying, Jonathan,

Here is what I was thinking:

Rather than have a table which shows this:

Column 1, Column 2, Column 3, Column Last updated

....

(with data in each column and the lastupdated column with getdate() )

have the following table:

Column 1, Column 2, Column 3

....this table gets updated,

--> Invokes a trigger/response to insert a getdate() in another table with 1 column which just has the date.

That way I can see an actual table of all the various times the Main table, with all the data, has been updated,

Once again, I appreciate your help.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

If all you want to know is how many times an update was run, and you don't care specifically which row(s) were affected then that would meet your need just fine and I don't see any reason you would have a problem implementing that.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

umanpowered (10/28/2008)
...But rather than have such data appear on every single row in the table, is there some code I can use to have the updated table trigger the creation of another table (like a one-row table) which shows the Date Time?

This may sound like a good idea and simpler at first, however, in almost every respect it is slower, more complicated and produces more problems.

I'd rather use such a one-row/datetime solution because I can easily "Objectify" its content on the ASP pages I write .

If that is the issue, then there is a better solution: Add a "LastUpdated" column to your table(s), have your update code set that with GetDate() when you Update (or write a trigger to do it). Add an index to your table(s) only on the "LastUpdated" column (ASC). Now create a view like so:

CREATE VIEW vwMainTable_LastUpdated

AS

SELECT MAX(LastUpdated) as [TableLastUpdated] From MainTable

Now use this view as your table to objectify.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

OP_HELP SSCrazy Points: 2618 More actions · Answer 9

rbarryyoung (10/29/2008)

umanpowered (10/28/2008)
... Add an index to your table(s) only on the "LastUpdated" column (ASC). Now create a view like so:
CREATE VIEW vwMainTable_LastUpdated
AS
SELECT MAX(LastUpdated) as [TableLastUpdated] From MainTable
Now use this view as your table to objectify.

I appreciate your advice. I've established the "LastUpdated", and I see it in the table. I coded the "getDate()" formula on this column. However, when I try to add the column as an Index, I get the following error (rendered in Visual Studio)

"Create failed for Index 'LastUpdate'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

"An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Connection. Info)

Column 'LastUpdate:' in table 'MyTable' cannot be used in an index or statistics or as a partition key because it is non-deterministic (Microsoft SQL Server, Error: 2729)

I tried to modify the column's properties, and I see the entry for "Deterministic", but through Visual Studio, I'm unable to set it to "Yes". So at this point, i cannot add the column. Any suggestions at this point? I will def. pursue your suggestion if I can get this to work, thanks.

-uman

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

It sounds to me like you added the column incorrectly to the table, as a computed column with the value of getdate() which is non-deterministic. It should have been created with a default constraint of getdate(), and not with a value of getdate(). Consider the following example:

use tempdb

create table temp1

(rowid int identity primary key,

lastupdate as getdate())

create table temp2

(rowid int identity primary key,

lastupdate datetime default(getdate()))

insert into temp1 default values

insert into temp2 default values

waitfor delay '00:00:05'

insert into temp1 default values

insert into temp2 default values

waitfor delay '00:00:05'

select * from temp1

select * from temp2

-- http://msdn.microsoft.com/en-us/library/ms174968.aspx

select columnproperty(object_id('temp1'), 'lastupdate', 'IsDeterministic')

-- this returns 0 because the column is not deterministic.

select columnproperty(object_id('temp2'), 'lastupdate', 'IsDeterministic')

-- this returns null because it is not a computed column it is persisted on the table.

drop table temp1

drop table temp2

You need to look at the CREATE TABLE DDL for the table that you added the column to and see if you added it as a computed column or a perisisted column with a default value. You can't change whether a column is deterministic or not by adjusting a property. This is determined by the database engine itself based on the way the column was created. If the output is guaranteed for the same inputs consistently over time, then the column is considered to be deterministic. Otherwise it isn't.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

As Jonathan said: "getdate()" needs to be the definition of the column's default value, and not the definition of the column itself.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

OP_HELP SSCrazy Points: 2618 More actions · Answer 12

Rbarry young and Jonathan,

Once again, I appreciate your assistance. Forgive me that I'm still on a learning curve with SQL and the Visual Studio IDE. After reviewing your suggestions, I took the following action:

I've posted below the interface of the "lastupdate" column description that I created in Visual Studio:

From what I see by the code you provided:

create table temp2

(rowid int identity primary key,

lastupdate datetime default(getdate()))

I attempted to take your code and input it into the interace. I could not change the "Is Idenity" to Yes through the interface. Nor could I determine how to modify the interface to account for the 'rowid' and 'int' properties.

So, I attempt to write a DDL SQL, modifying the current table to add a new column in the following fashion:

ALTER TABLE [Devices]

ADD lastupdate

(rowid int identity primary key,

datetime default(getdate()))

When I parse the above code I get the following error:

The definition for column 'lastupdate' must include a data type.

So, from the DDL and the interace, I'm unable to add the column. I was thinking that these properties would be accepted based upon what you had written. I may not properly understand how the properities of CREATE DDLS may differ from ALTER, so please forgive me.

I'd rather not re-create the definition of the table, if possible.

Do you have any suggestions of where to go from here?

Once again, many thanks. Its because of the contributors to this forum that I've been able to achieve my goals.

-uman powered.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Try this command instead:

ALTER TABLE [Devices]

ADD lastupdate datetime default(getdate())

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

OP_HELP SSCrazy Points: 2618 More actions · Answer 14

R BARRY YOUNG;

Thanks! The code you provided

ALTER TABLE [testData]

ADD lastupdate datetime default(getdate())

worked!

I configured the TableAdapter with your suggested implementation:

Update testData

SET note=@note, lastupdate = getdate()

linked it to an ObjectDataSource on my .aspx page, linked that to my GridView with Editing on; read-only for the lastupdate column.

The updates were implemented; they come back on the postback to the .aspx page as well as on the underlying Table in the DB

Your help was greatly appreciated. I'll have to come back and review your other suggestions.

-uman