I need some assistance with a update trigger to insert the UserId and current DateTime into the Update statement automatically.
In PostgreSQL I use the following Function/Stored Procedure and trigger statement. Can someone help me with the equivilant Stored Procedure in SQL Server 2008 R2?
Function / Stored Procedure
CREATE OR REPLACE FUNCTION "Security"."UpdateStamp"() RETURNS trigger as $UpdateStamp$
SELECT "IDUser" INTO UserID FROM "Security"."User" WHERE "Username" = current_user;
NEW."LastUpdateBy" := UserID;
NEW."LastUpdateTime" := current_timestamp;
$UpdateStamp$ LANGUAGE plpgsql STABLE;
PostgreSQL Trigger Example
CREATE TRIGGER UpdateStamp BEFORE UPDATE ON "Person"."Name"
FOR EACH ROW EXECUTE PROCEDURE "Security"."UpdateStamp"();
Assumption: the variable @userid contains the UserID FROM "Security"."User". this value has to be retrieved either in the update stored procedure or, retrieved in the caller and sent as an input parameter to the update stored procedure.
CREATE TRIGGER tr_DaTable ON dbo.DaTable
AFTER UPDATE AS
DECLARE @UserID integer
-- SELECT @UserID = [Security].[User].[IDUser]
-- FROM [Security].[User]
-- INNER JOIN sys.sysprocesses ON
-- [Security].[User].[Username] = sys.sysprocesses.NT_UserName
-- WHERE spid = @@SPID
SELECT @UserID = uid --,NT_UserName, LogiName
where spid = @@SPID
SET LastUpdateBy = @UserID,
LastUpdateTime = GetDate()
FROM DaTable U
INNER JOIN inserted i ON
U.Id = i.Id
You should uncomment the first @UserID assignment.
Play a bit with SELECT uid , NT_UserName, LogiName
where spid = @@SPID
to find out what column you need.
I have played a little bit with PostgreSQL. I found the trigger syntax unlike anything I was familiar with (Informix, DB2, MSSQL). Thank you for this example.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages