Better way to write this trigger

[timscronin]

The trigger below works. I was wondering for performance reasons is there a better way to try this. The idea is that it will fire if anyone other than the system(dbo) updates it to trap the time and id.

CREATE trigger tr_cmsUpdt_tbnew on ppdnew for update as
-- updates record with sql user and timestamp
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
if @muser <> 'dbo'
begin
UPDATE ppdnew
set puser_id = @muser, record_timestamp = getdate()
from deleted dt WHERE
ppdnew.ppd_rec_id = dt.ppd_rec_id
end
end

 

[tlbroadbent]

I don't see much to improve performance but I have some suggestions and a question.

1) You declare 2 variables which are not used. Eliminate them.
2) Use a complete JOIN syntax to eliminate ambiguity and avoid misundestanding the update query.
3) QUESTION: Do you want to use the deleted virtual table or the inserted table? Most of the time, one would the inserted table. However, if ppd_rec_id is a Primary Key, it should matter.

CREATE Trigger tr_cmsUpdt_tbnew
ON ppdnew FOR UPDATE AS
-- updates record with sql user and timestamp

DECLARE @muser varchar(35)
SET @muser = current_user

IF @muser <> 'dbo'
BEGIN
UPDATE ppdnew
SET puser_id = @muser,
record_timestamp = getdate()
FROM ppdnew p INNER JOIN deleted dt
ON p.ppd_rec_id = dt.ppd_rec_id
END
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm