Trigger Problems

[storm75m]

I have a table named AcctTypes with these fields defined:

AcctID
Name
Location
Phone
ModUsr
ModDate

Any time that the data is changed in any field in this table, I would like to update the ModUsr to be the current username, and ModDate to be today's date/time. I've never created a trigger before, and my first attempt at this is continuously giving me Write Conflict errors. Here is what I had so far:

CREATE TRIGGER [TR_AcctTypes_Update] ON [dbo].[AcctTypes]
AFTER UPDATE
AS
set nocount on
UPDATE AcctTypes
SET ModUsr = SUSER_SNAME(), ModDate = GETDATE()

This is giving me an error almost every time I try to update a record, and no one seems to know what's wrong... (I've posted this problem before, and googled this to death) Any help would be much appreciated...

 

[jgoodman00]

CREATE TRIGGER [TR_AcctTypes_Update] ON [dbo].[AcctTypes] 
AFTER UPDATE 
AS
UPDATE    AcctTypes
SET ModUsr = SUSER_SNAME(), 
    ModDate = GETDATE()
FROM AcctTypes INNER JOIN inserted
ON AcctTypes.PKField = inserted.PKField

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[storm75m]

Thanks, I'll give this a shot. What if my Primary key is defined on two fields?

 

[jgoodman00]

I used PKField just for illustrative purposes.

It is a standard SQL statement, & you just need to modify as appropriate in order to only modify the records you want to modify.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com