Record Update Date - 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

General SQL Server Forums

New to SQL Server Programming

Record Update Date

Author

Topic

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-02 : 04:49:32

How to update the Last Update Date of a modified record? I want to put this in table trigger

.

Or any setting can be used?

Please help~~~~

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-11-02 : 05:15:21

Create an update trigger that updates the date column.

Could you paste your code here.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 05:18:53

Something like this

Update YT set YT.DateCol=Getdate() from yourTable YT inner join inserted I on YT.keycol=I.keycol

Otherwise post table structure

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-11-02 : 05:54:43

If you do your UPDATEs through a Stored Procedure then better to change the Update Date there - a trigger will make the update "twice" - the first one is the normal update, the second one is the trigger's update. Also, if you bulk import data from somewhere else it will get "now" as the Update Date - that can be good, or bad. For us its "bad" because we want the update date from the "remote database" to be preserved.

Here is our standard trigger template (which includes copying the previous version of the record to an Audit Table)


--
-- Globally change:
--	MyTrigger	Name of trigger
--	MyTable		Name of table
-- 	MyCreateDate	Create Date Column
-- 	MyCreateUser	Create User Column
-- 	MyUpdateDate	Update Date Column
-- 	MyUpdateUser	Update User Column
--	MyPrimaryKey	Name of Primary Key Column (modify the code if multi-part PK)
--	MyAuditTable	Name of Audit Table
--			NOTE: Audit Table needs two addition columns (at start of column list):
--			MyActionCode char(1), MyAuditDate datetime
--			and then all the same columns, in the same order, as the main table
--	Search for "TODO" and perform the required action, then delete the TODO comment
--
PRINT 'Create trigger MyTrigger'
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'MyTrigger' AND type = 'TR')
	DROP TRIGGER dbo.MyTrigger
GO
CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
/* WITH ENCRYPTION */
AFTER INSERT, UPDATE, DELETE
AS
/*
 * MyTrigger	Trigger for MyTable
 *
 * HISTORY:
 *
--TODO Set Date and Initials here
 * 02-Nov-2005 XXX  Started
 */
SET NOCOUNT ON
SET XACT_ABORT ON 
SET ARITHABORT ON 

DECLARE	@dtNow		datetime,
--TODO Set the max width of the User Name column here (longer user_name() will be truncated)
	@strUser	varchar(50)

	SELECT	@dtNow = GetDate(),
		@strUser = user_name()

	UPDATE	U
	SET
-- Set Create/Update dates and users
		[MyCreateDate]	= COALESCE(I.MyCreateDate, @dtNow),	-- Set only IF NULL
		[MyCreateUser]	= COALESCE(I.MyCreateUser, @strUser),	-- Set only IF NULL
		[MyUpdateDate]	= I.MyUpdateDate, 
		[MyUpdateUser]	= I.MyUpdateUser
	FROM	inserted AS I
		JOIN dbo.MyTable AS U
			 ON U.MyPrimaryKey = I.MyPrimaryKey

	-- AUDIT:Store any previous version (including where record is being deleted)
	INSERT dbo.MyAuditTable
	SELECT	CASE WHEN I.MyPrimaryKey IS NULL THEN 'D' ELSE 'U' END, @dtNow, D.*
	FROM	deleted AS D
		LEFT OUTER JOIN inserted AS I
			 ON I.MyPrimaryKey = D.MyPrimaryKey

/* TEST RIG

INSERT INTO MyTable ( COLUMN LIST NEEDED ) values ( TEST VALUES LIST NEEDED )

SELECT TOP 10 * FROM dbo.MyTable ORDER BY MyUpdateDate DESC

 */
--================== MyTrigger ==================--
GO
PRINT 'Create trigger MyTrigger - DONE'
GO
--

Kristen

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-02 : 20:38:44

My table has User ID, User Role, User Access, Create Date, and Last Update Date. The table trigger is:


CREATE TRIGGER tr_UserInfo_Update ON dbo.UserInfo
FOR UPDATE
AS

Begin

  Update UserInfo
    set UpdateDate = getdate ()
    where ... (I have problem here)

End

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-02 : 22:45:29

I found some sample codes using table name "insert" (as in Kristen's example), "updated", "deleted". Are they the SQL Server system tables to hold the inserted/updated/deleted records?

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-02 : 22:56:35

With reference to Kristen's example, may I use:

Update UserInfo
    set UpdateDate = getdate ()
    where exists (select * from Updated)

Can this identified the modified record

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-03 : 01:19:18

oh~ no

I changed the trigger as above, using (select from updated), but got following error page when saving the record in the web application

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Invalid object name 'Updated'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-03 : 02:16:33

CREATE TRIGGER tr_UserInfo_Update ON dbo.UserInfo
FOR UPDATE
AS

Begin

Update UserInfo
set UpdateDate = getdate ()
from inserted i, UserInfo a
where i.pk1 = a.pk1

End

[KH]

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-03 : 05:26:28

I did it!! thanks KH

One more question, what if the table's PK is combined of 2 columns?

Kristen
Test

22859 Posts

Posted - 2005-11-03 : 08:18:05

"CREATE TRIGGER tr_UserInfo_Update ON dbo.UserInfo
FOR UPDATE"

You don't want the update set for newly inserted records?

"from inserted i, UserInfo a
where i.pk1 = a.pk1"

Don't use this format, use the JOIN style in my example.

You also need, at the very least, "SET NOCOUNT ON" otherwise you will get spurious resultsets which may muck up your application. You'd be better off with the other bits in my example too, but that's up to you.

Kristen

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-03 : 10:56:33

Kristen, the new record will have the update date too.

If I use AFTER UPDATE, that means the table will be updated(by trigger) again after performing the update(by transaction)? Will it cause an endless loop?

Kristen
Test

22859 Posts

Posted - 2005-11-03 : 12:40:11

No, doesn't work like that. Update to the table itself, in its own trigger, do not recursively call the trigger, so its safe to update the table itself on either INSERT or UPDATE.

Kristen

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-03 : 19:59:00

Kristen, thanks for your explanation.

I'm not sure if I use getdate() as the default value of the create date column, is this a good way to do it?

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-03 : 23:04:53

what if the PK is combined of 2 or more columns?
eg. UserID + CustID

It seems I just can't simple use "and" to include them

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-04 : 05:27:36

Update UserInfo
set UpdateDate = getdate ()
from inserted i inner join UserInfo a
on i.UserID = a.UserID
and i.CustID = a.CustID

[KH]

Kristen
Test

22859 Posts

Posted - 2005-11-06 : 07:54:07

"I'm not sure if I use getdate() as the default value of the create date column, is this a good way to do it?"

Yup, that's a good plan. But an INSERT that specifically supplies a Date in that column will be preserved - whereas with the trigger you can have it force a value in for newly created records - if you want to.

Personally the DEFAULT would do me - if I explicitly provide a date (e.g. copying records from some other database) then I almost certainly want to keep the original create date.

"what if the PK is combined of 2 or more columns?"

As khtan said (although I don't think "Update UserInfo" will work because that table has been aliased to "a"), just for completeness using my code above as an example:


	FROM	inserted AS I
		JOIN dbo.MyTable AS U
			 ON U.MyPrimaryKey1 = I.MyPrimaryKey1
			AND U.MyPrimaryKey2 = I.MyPrimaryKey2

Kristen

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-06 : 20:20:06

I wrote this in my table trigger:

  Update UserRole 
   set UpdDat = @dtNow
  from inserted as i
  JOIN dbo.UserRole as r
    ON    i.UserID       = r.UserID
    and   i.CustMasterID = r.CustMasterID
    and   i.CustID       = r.CustID

Only the first column (UserID) is being used as key, that means the update date of all customers under the same user will be updated.

Kristen
Test

22859 Posts

Posted - 2005-11-07 : 00:37:53

As I said before I'm surprised that using

Update UserRole

and then aliasing that table:

JOIN dbo.UserRole as r

works, but I haven't tried it to prove it one way or the other!

Kristen

nicole
Yak Posting Veteran

97 Posts

Posted - 2005-11-07 : 02:55:03

I tried all the suggested ways, but only the first column is taken as the key

Or please share how you guys update the record update date, not necessarily in table trigger.

Kristen
Test

22859 Posts

Posted - 2005-11-07 : 12:23:16

[code]
UPDATE U
SET UpdDat = @dtNow
FROM inserted AS I
JOIN dbo.UserRole AS U
ON U.UserID = I.UserID
AND U.CustMasterID = I.CustMasterID
AND U.CustID = I.CustID
[/code]
Test with:
[code]
BEGIN TRANSACTION
UPDATE U
SET UpdDat = GetDate()
FROM dbo.UserRole AS U
WHERE U.UserID = 'SomeValue'
AND U.CustMasterID = 'SomeValue'
AND U.CustID = 'SomeValue'
SELECT [Row Count] = @@ROWCOUNT
ROLLBACK
[/code]
If you do not get a Row Count of 1 then your vales for the three columns do not map to a unique records

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources