June 2, 2008 at 1:39 am
Hi Guys,
I need a help, i need to write a trigger for a table on insert, the details i require is the user who has entered the recored, can i get it is there any way i can get this infor.
Thanks in Advance
Regards,
Venu
June 2, 2008 at 1:56 am
you can write an insert trigger on the table that records the CURRENT_USER to the same table or another table.
June 2, 2008 at 1:58 am
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/29248949-325b-4063-9f55-5a445fb35c6e.htm
June 2, 2008 at 2:09 am
Rajan John (6/2/2008)
you can write an insert trigger on the table that records the CURRENT_USER to the same table or another table.
Just make sure you do not use EXECUTE AS in your trigger, as in that case "CURRENT USER" will return the impersonated user name.
Regards,
Andras
June 2, 2008 at 2:31 am
Hi,
Thanks for the reply, Check the below i have written a trigger that send a mail whenever a new record is added, along with this data i need to get the user who inserted the record, please review is this the right approach
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create TRIGGER [New_Item_Alert] on Table1 for insert
as
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
CAST((
SELECTtd = Item_No, '',
td = itemdesc, '',
td= CURRENT_USER
FROM inserted
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients='test@test.com',
@subject = 'New Item Created',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name ='POSTMAIL'
June 2, 2008 at 3:05 am
Guys,
I got it with the SQL function SYSTEM_USER which solved my issue.
Thanks for all of you who replied my query, without you guys i was not able to trace.
Try this out
DECLARE @sys_usr char(30);
SET @sys_usr = SYSTEM_USER;
SELECT 'The current system user is: '+ @sys_usr;
GO
Thanks and Regards
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply