Get the UserID of the current logged in User in asp.net default membership provider

I have two tables

Employee (EmpID, Name, UpdatedBy, UpdateDate)
EmpContact (ContactID, Contact,EmpID)

I am trying to create a trigger which update a Employee table fields UpdatedBy,UpdateDate. when contact is added or modified i want to update Employee's UpdatedBy and UpdateDate fields (These two tables have different asp.net-mvc views). I am using default membership provider to authenticate the user.

So my question is how can i get the current logged in user in trigger who initiated the insert, upda开发者_运维知识库te or delete. Is there any way i can know which asp.net user id initiated the transaction within the TRIGGER

Create an insert/update/delete trigger that updates the Emplyoee table when records in EmpContact are created/updated/deleted, for example(insert):

CREATE TRIGGER [dbo].[trgCreateEmpContact] ON [dbo].[EmpContact]
FOR INSERT
AS

UPDATE dbo.Employee
SET UpdateDate=GetDate(), UpdatedBy = I.UpdatedBy
FROM Employee E
INNER JOIN Inserted I ON E.EmpID = I.EmpID

So you need to add the UpdatedBy column to your EmpContact table too.

You get the current logged in user in the following way:

MembershipUser currentUser = Membership.GetUser();
GUID currentUserID = (GUID)currentUser.ProviderUserKey;

Another(better) approach would be to create a stored-procedure that internally creates the EmpContact record and updates the Employee table in a transaction, for example(untested):

CREATE PROCEDURE [dbo].[InsertEmpContact]
    @ContactID int OUTPUT,
    @Contact varchar(50) OUTPUT,
    @EmpID int OUTPUT,
    @UpdateDate datetime OUTPUT,
    @UpdatedBy int OUTPUT,
 with execute as Owner
AS
BEGIN TRANSACTION

INSERT INTO EmpContact(Contact, EmpID)
VALUES     (@Contact,@EmpID)

;SELECT  @ContactID=ContactID,@Contact=Contact,@EmpID=EmpID,@UpdateDate=GetDate()
FROM EmpContact WHERE (ContactID = SCOPE_IDENTITY())

;UPDATE dbo.Employee
SET UpdateDate=@UpdateDate, UpdatedBy = @UpdatedBy
WHERE EmpID = @EmpID 

IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK
    RETURN
 END

COMMIT

If you want to detect the current logged in user from memebership please use these links

http://www.codeproject.com/KB/aspnet/CustomMembershipProviders.aspx

http://theintegrity.co.uk/2010/11/asp-net-mvc-2-custom-membership-provider-tutorial-part-2/

It depends on how your app connects to your database. If you do it, like almost all of us, with connection pooling and a single user connecting to the database, the answer is no, you cannot know that in the trigger.

E.g. the app connects to the database as sa/pwd (not advisable, but for arguments sake only).

If user one (Paul) connects to the db and does an update, your trigger will get sa as user. If user two (Anne) connects to the db, the trigger will also get sa as user.

So you need to include the update of the user in the update statmement (or as an argument to your stored procedure)