More Efficient Trigger 1

[cfw2]

Hello,
I am adding a trigger for a table that updates a "status" field on the table being updated, as well as the update ID and update date.

In addition, I need to log the updates in a separate table to track all changes, not just the most recent. Here is what I have. This works, however I was wondering if there might be a better or more efficient way to do this.

Thank you in advance,
Charles

create trigger store_update
	ON dbo.store_tab
  FOR UPDATE AS
 BEGIN
	  begin tran
		update b
		set 
		 b.last_update_id = upper(case when len(c.nt_username) = 0 then c.loginame else c.nt_username end)
		 ,b.last_update_date = getdate()
		from inserted a
		 inner join store_tab b
		  on a.store = b.store
		 inner join master..sysprocesses c
		  on 1=1
		where c.spid = @@spid
	  commit tran

	  begin tran
		insert into dbo.store_log
				( store ,transdate ,storenum ,regnum ,transnum ,status ,update_id ,update_date )
		select distinct
		 store
		 ,transdate
		 ,storenum
		 ,regnum
		 ,transnum
		 ,status
		 ,upper(case when len(c.nt_username) = 0 then c.loginame else c.nt_username end)
		 ,getdate()
		from inserted a
		 ,master..sysprocesses c
		where c.spid = @@spid
	  commit tran


 END
GO

 

[SQLSister]

get rid of the joins to sysprocesses. It will be the same user for all records correct as it is all one transaction. no need to doa case on each and every record in inserted. So do a select into a variable for the username and then use that variable inthe insert and update rather than joining to the table.

And stop using the join sysntax in the last one anywhere in your code, it is no longer supported in 2005, so start fixing your code now to avoid headlaches later when you upgrade.

Questions about posting. See faq183-874