December 1, 2014 at 7:52 am
i have a table called employee.and the columns are Name,SID,C,Date
and i created an audit log table on employee table.when ever in frontend any row is updated it will fire the trigger and update in log table.
My question is in the trigger i am inserting into this table tblAudit ...The @ID we are capturing here is who ever we updating the record those id but i need logged in user id ...suppose if i am updating another person record i need to capture my login id how to do this..do we need to add a new column?
ALTER TRIGGER [dbo].[trAudit] ON [dbo].[Employee]
AFTER UPDATE
AS
Declare @ID char(6),
@CurrDesc char(40),
@EID int
SET NOCOUNT ON
Select @ID = i.SID, @CurrDesc =i.Desc,@EID = i.Name From Inserted i
If (@CurrDesc ='' or @CurrDesc ='AVA' )
Begin
INSERT INTO tblAudit VALUES(@EID , @ID, @CurrDesc , GETUTCDATE())
End
else
Begin
INSERT INTO tblAudit VALUES(@EID , @ID, @CurrDesc , GETUTCDATE())
End
SET NOCOUNT OFF
December 1, 2014 at 11:01 am
well it depends on how users connect.
if everyone comes in from an application, and the application uses the same account for all users to connect, you have to get the "who" information from the application itself. there is no way, form aSQL perspective, to find the windows user of an app that is connecting with a SQL login.
if it's connecting with a sql or AD account for each user, there are a suite of functions you cna use within a trigger to get that;
here's some nice ones as an example:
for 2008 and above:
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
December 1, 2014 at 11:10 am
also, your trigger is poorly designed; it is not designed take into consideration multiple rows in a single update; it should be referencing the virtual INSERTED table, and not declared parameters and VALUES statements.
it will fail to audit all updates based on an update statement that affects more than one row.
maybe something like this, assuming suser_name and Employee.sid are the same values?
ALTER TRIGGER [dbo].[trAudit] ON [dbo].[Employee]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblAudit
SELECT
CASE
WHEN suser_name() = i.[SID]
THEN i.[SID]
ELSE suser_name()
END,
i.[Desc],
i.[Name],
GETUTCDATE()
FROM Inserted i
WHERE i.[Desc] ='' or i.[Desc] ='AVA'
END
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply