Trigger Update Stored Procedure

Wedgetail · #1 (**permalink**) 09-02-11, 20:37

Hi Forum,

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

Code:

CREATE OR REPLACE FUNCTION "Security"."UpdateStamp"() RETURNS trigger as $UpdateStamp$
    DECLARE
	UserID integer;
    BEGIN
	SELECT "IDUser" INTO UserID FROM "Security"."User" WHERE "Username" = current_user;
	NEW."LastUpdateBy" := UserID;
	NEW."LastUpdateTime" := current_timestamp;
	RETURN NEW;
    END;
$UpdateStamp$ LANGUAGE plpgsql STABLE;

PostgreSQL Trigger Example

Quote:

CREATE TRIGGER UpdateStamp BEFORE UPDATE ON "Person"."Name"
FOR EACH ROW EXECUTE PROCEDURE "Security"."UpdateStamp"();

baburajv · #2 (**permalink**) 09-05-11, 00:31

Hello Wedgetail,

If I understand the problem statement correctly, the requirement is to have the user id and the current date to be part of the "update" statement.

In that case, we do not need a trigger (assuming that the trigger does not do anything else) and a user defined function for this.

If there is a stored procedure to update the "Person"."Name" table, you can add the code to set the userid and datetime as

update <table>
set <column> = ...
LastUpdateBy= @userid,
LastUpdateTime = getdate()
[where]...

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.

Wim · #3 (**permalink**) 09-05-11, 07:11

This should get you started:

Code:

CREATE TRIGGER tr_DaTable ON dbo.DaTable 
AFTER UPDATE AS
BEGIN
    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 
	from sys.sysprocesses 
	where spid = @@SPID

	UPDATE U 
	SET LastUpdateBy = @UserID,
		LastUpdateTime = GetDate()
	FROM DaTable U 
		INNER JOIN inserted i ON 
			U.Id = i.Id
END

You should uncomment the first @UserID assignment.

Play a bit with
SELECT uid , NT_UserName, LogiName
from sys.sysprocesses
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.

Internet Services

Web Development

Digital Marketing

Consumer Tech