CREATE PROCEDURE AuditDatabase_LogApplicationOperation
    @ApplicationOperation	VARCHAR(10),
    @ApplicationOperationInfo 	VARCHAR(100)
AS
BEGIN
DECLARE
	@DBMSTransaction VARCHAR(255),
	@OSUSER VARCHAR(50), 
	@DBMSUSER VARCHAR(50), 
	@HostPhysicalAddress VARCHAR(17), 
	@contexto varchar(128),
	@ApplicationModifierUser varchar(50),
	@SessionInfo_OSUser varchar(50),
	@HostLogicalAddress varchar(30)

Set NOCOUNT On

IF @@trancount>0
BEGIN
	EXECUTE sp_getbindtoken @DBMSTransaction OUTPUT
END
ELSE BEGIN
	SET @DBMSTransaction = NULL
END

IF PatIndex( '%\%',SUSER_SNAME()) > 0
BEGIN
	set @OSUSER = SUSER_SNAME()
	set @DBMSUSER = NULL
END
ELSE BEGIN
	SET @OSUSER = NULL
	SET @DBMSUSER = SUSER_SNAME()
END

set @HostPhysicalAddress = (SELECT net_address FROM master..sysprocesses where spid=@@spid )
set @HostPhysicalAddress = substring (@HostPhysicalAddress,1,2) + '-' + substring (@HostPhysicalAddress,3,2) + '-' + substring (@HostPhysicalAddress,5,2) + '-' + substring (@HostPhysicalAddress,7,2) + '-' + substring (@HostPhysicalAddress,9,2) + '-' + substring (@HostPhysicalAddress,11,2)

SELECT @contexto=CAST(context_info AS varchar(128)) FROM master..sysprocesses WHERE spid=@@SPID
IF (PatIndex( '%APPLICATION_USER=%',@contexto) is not null) and (PatIndex( '%APPLICATION_USER=%',@contexto) > 0)
	set @ApplicationModifierUser=substring(ltrim(substring(@contexto,PatIndex( '%APPLICATION_USER=%',@contexto)+17,128)),1, charIndex( '///',ltrim(substring(@contexto,PatIndex( '%APPLICATION_USER=%',@contexto)+17,128) ) ) - 1 )
ELSE
	set @ApplicationModifierUser=NULL
IF (PatIndex( '%OS_USER=%',@contexto) is not null)  and ( PatIndex( '%OS_USER=%',@contexto)>0 )
	set @SessionInfo_OSUser=substring(ltrim(substring(@contexto,PatIndex( '%OS_USER=%',@contexto)+8,128)),1, charIndex( '///',ltrim(substring(@contexto,PatIndex( '%OS_USER=%',@contexto)+8,128) ) ) - 1 )
ELSE
	set @SessionInfo_OSUser=NULL
IF (PatIndex( '%LOGICAL_ADDRESS=%',@contexto) is not null) and (PatIndex( '%LOGICAL_ADDRESS=%',@contexto)>0)
	set @HostLogicalAddress=substring(ltrim(substring(@contexto,PatIndex( '%LOGICAL_ADDRESS=%',@contexto)+16,128)),1, charIndex( '///',ltrim(substring(@contexto,PatIndex( '%LOGICAL_ADDRESS=%',@contexto)+16,128) ) ) - 1 )
ELSE
	set @HostLogicalAddress=NULL

SET @ApplicationOperationInfo = rtrim(@ApplicationOperationInfo)
	INSERT INTO AuditedOperations ( Application, Object, OperationType, ModifiedDate, ApplicationModifierUser, OSModifierUser, DBMSModifierUser, Host, HostLogicalAddress, HostPhysicalAddress, DBMSTransaction, ApplicationOperation, ApplicationOperationInfo)
VALUES (APP_NAME(), NULL, 'A', GETDATE(), @ApplicationModifierUser, @OSUSER, @DBMSUSER, HOST_NAME(), @HostLogicalAddress, @HostPhysicalAddress, @DBMSTransaction, @ApplicationOperation, @ApplicationOperationInfo)

END
GO