SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Record Update Date
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/02/2005 :  04:49:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 11/02/2005 :  05:15:21  Show Profile  Reply with Quote
Create an update trigger that updates the date column.

Could you paste your code here.

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 11/02/2005 :  05:18:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/02/2005 :  05:54:43  Show Profile  Reply with Quote
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
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/02/2005 :  20:38:44  Show Profile  Reply with Quote
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
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/02/2005 :  22:45:29  Show Profile  Reply with Quote
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?
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/02/2005 :  22:56:35  Show Profile  Reply with Quote
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
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/03/2005 :  01:19:18  Show Profile  Reply with Quote
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'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 11/03/2005 :  02:16:33  Show Profile  Reply with Quote
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]
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/03/2005 :  05:26:28  Show Profile  Reply with Quote
oh I did it!! thanks KH

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

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/03/2005 :  08:18:05  Show Profile  Reply with Quote
"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

Edited by - Kristen on 11/03/2005 08:18:26
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/03/2005 :  10:56:33  Show Profile  Reply with Quote
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?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/03/2005 :  12:40:11  Show Profile  Reply with Quote
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
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/03/2005 :  19:59:00  Show Profile  Reply with Quote
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?
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/03/2005 :  23:04:53  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 11/04/2005 :  05:27:36  Show Profile  Reply with Quote
Update UserInfo
set UpdateDate = getdate ()
from inserted i inner join UserInfo a
on i.UserID = a.UserID
and i.CustID = a.CustID



[KH]
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/06/2005 :  07:54:07  Show Profile  Reply with Quote
"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
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/06/2005 :  20:20:06  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2005 :  00:37:53  Show Profile  Reply with Quote
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
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/07/2005 :  02:55:03  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2005 :  12:23:16  Show Profile  Reply with Quote

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

Test with:

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

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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000