| Author |
 Topic  |
|
nicole
Yak Posting Veteran
Hong Kong
97 Posts |
 Posted - 11/07/2005 : 21:10:29
|
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.
|
 |
|
|
nicole
Yak Posting Veteran
Hong Kong
97 Posts |
Posted - 11/07/2005 : 21:17:34
|
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...
|
|
|
|
nicole
Yak Posting Veteran
Hong Kong
97 Posts |
Posted - 11/07/2005 : 21:41:07
|
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  |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/08/2005 : 01:07:18
|
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 |
|
|
|
nicole
Yak Posting Veteran
Hong Kong
97 Posts |
Posted - 11/08/2005 : 05:09:42
|
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
|
|
|
|
nicole
Yak Posting Veteran
Hong Kong
97 Posts |
Posted - 11/08/2005 : 05:25:53
|
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
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/08/2005 : 06:25:00
|
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 |
|
|
|
nicole
Yak Posting Veteran
Hong Kong
97 Posts |
Posted - 11/08/2005 : 21:31:32
|
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 
|
|
|
|
MACH9
Starting Member
Canada
1 Posts |
Posted - 05/15/2011 : 14:39:42
|
Thank you for the information, it was very helpful.
William. |
|
|
|
Topic |
|
Record Update Date
