Trigger Problem 1

[niall29]

I have a trigger which looks like this

CREATE TRIGGER [Processes_Trigger] ON [dbo].[Processes]
FOR INSERT, UPDATE
AS
Update Processes
Set modified_Date = GetDate(),
modified_User = (select loginame=rtrim(loginame)
from master.dbo.sysprocesses
where spid = @@spid)

but when I insert a record or update a record it updates the 2 colums great then when I close the table and open it again the 2 colums are updated in all the records. Please can you tell me what I'm doing wrong

Thanks in advance

 

[mrdenny]

You trigger is not specifying what record to update. Try this. My changes are in red.

CREATE TRIGGER [Processes_Trigger] ON [dbo].[Processes] 
FOR INSERT, UPDATE
AS
Update Processes
Set modified_Date = GetDate(),
 modified_User = [COLOR=red]SYSTEM_USER
where {PrimaryKey} in (select {PrimaryKey} from inserted)
[/color]

You'll also notice that I removed the query to the sysprocesses table and used the SYSTEM_USER function to find out the login of the person who is running the query. This will keep you from having to worry about locks on the sysprocesses table slowing you down.

Denny

--Anything is possible. All it takes is a little research. (Me)