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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/07/2005 :  21:10:29  Show Profile  Reply with Quote
The above 3 columns can identify an unique record.

My table records show:

User    Customer   Cust   Last Update Date
------  --------   ----   ----------------
Nicole     A        A1    18:38:12.047
Nicole     B        B1    18:38:12.030
Nicole     C        C1    18:38:12.030

Customer "A" is the one which I modified thru application. It seems the other 2 records are being updated somewhere before the trigger executed.
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/07/2005 :  21:17:34  Show Profile  Reply with Quote
I found that the "UpdateDate" column was being assigned in program. So, I removed that part of scripts (and clear the table trigger too) and test run again; the result is normal ("UpdateDate" is not updated).

However, after the table trigger created, I got the above result...
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/07/2005 :  21:41:07  Show Profile  Reply with Quote
I'm not sure how the update trigger fires, per record or per update (i.e. if batch update, it fires only once or once for each record?)

Can it have output arguments
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/08/2005 :  01:07:18  Show Profile  Reply with Quote
It should fire once for each UPDATE / INSERT statement.

So

UPDATE MyTable SET MyColumn1 = 'FOO' WHERE MyColumn2 = 'BAR'

may update many records, but the trigger will only fire once; "inserted" & "deleted" will contain copies of ALL the records effected.

In your example data the Last Update Date for "A" is more recent than "B" and "C". To the best of my knowledge in a single update statement ALL the rows will be set to the same value for GetDate(), so "B" and "C" must have been set by a different UPDATE statement

Please post the code from your trigger exactly as you have it, then we can see if we can spot something wrong.

Kristen
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/08/2005 :  05:09:42  Show Profile  Reply with Quote
I tried to execute the trigger when UPDATE only, will handle NEW record after the problem fixed.
CREATE TRIGGER tr_UserRole_Ins_Upd ON dbo.UserRole 
AFTER UPDATE
AS

SET NOCOUNT ON
SET XACT_ABORT ON

Begin

  Update UserRole
    set UpdDat = getdate ()
    from inserted
    where inserted.UserID       = UserRole.UserID
    and   inserted.CustMasterID = UserRole.CustMasterID
    and   inserted.CustID       = UserRole.CustID

End

Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/08/2005 :  05:25:53  Show Profile  Reply with Quote
I really don't understand

When I removed the trigger, the record update date will not be updated, as I expected. But after I added the above trigger, records under the same user will be updated somehow. Does the minor difference of the update time tells something?

Anyone can help to tell me why
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/08/2005 :  06:25:00  Show Profile  Reply with Quote
As I posted before (but changing the date from a local variable to the GetDate() function):

UPDATE U
SET	UpdDat = @dtNow GetDate()
FROM	inserted AS I
	JOIN dbo.UserRole AS U
		 ON U.UserID = I.UserID
		AND U.CustMasterID = I.CustMasterID
		AND U.CustID = I.CustID

Kristen
Go to Top of Page

nicole
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 11/08/2005 :  21:31:32  Show Profile  Reply with Quote
I think my problem should be solved a few days ago

Why I say that? Since I tested the trigger by saving the record from the application, not by running query on SQL Query Analyser, and I simply click the [Save] button, an update will be triggered.

Today, luckily, I found something wrong about the program, so I need to update the record directly to the table (that means using SQL Query Analyser). Then I found that the "Last Update Date" was updated correctly (only to the record I modified)

Now I have to focus on the program, not the trigger anymore

Thanks all for the help and have a nice day
Go to Top of Page

MACH9
Starting Member

Canada
1 Posts

Posted - 05/15/2011 :  14:39:42  Show Profile  Reply with Quote
Thank you for the information, it was very helpful.

William.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.06 seconds. Powered By: Snitz Forums 2000