Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    29

    Question Unanswered: Trigger Update Stored Procedure

    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
    CREATE TRIGGER UpdateStamp BEFORE UPDATE ON "Person"."Name"
    FOR EACH ROW EXECUTE PROCEDURE "Security"."UpdateStamp"();

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    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.
    Cheers....

    baburajv

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •