SQL Server 2008基于子字符串的审计触发器

Question

我想创建一个基于列的触发器，但只针对那些以_ess结尾的记录。如何设置审计触发器来执行此操作?

这是当前的触发器，但它只是检查所有更改的用户名，而我只想检查用户名何时更新或从以_ess结尾的用户名

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AUDIT_UPD_HRPERSONS_USERNAME] ON [dbo].[HRPersons] FOR UPDATE NOT FOR REPLICATION As
BEGIN
DECLARE
@OperationNum   int,
@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
INSERT INTO AuditedOperations ( Application, Object, OperationType, ModifiedDate, ApplicationModifierUser, OSModifierUser, DBMSModifierUser, Host, HostLogicalAddress, HostPhysicalAddress, DBMSTransaction)
VALUES (APP_NAME(), 'HRPERSONS', 'U', GETDATE(), @ApplicationModifierUser, @OSUSER, @DBMSUSER, HOST_NAME(), @HostLogicalAddress, @HostPhysicalAddress, @DBMSTransaction)
Set @OperationNum = @@IDENTITY
INSERT INTO AuditedRows (OperationNum, RowPK)
SELECT @OperationNum, ISNULL(CAST(INSERTED.ID as nvarchar),CAST(DELETED.ID as nvarchar))
FROM INSERTED FULL OUTER JOIN DELETED ON INSERTED.ID=DELETED.ID
INSERT INTO AuditedRowsColumns (OperationNum, RowPK, ColumnName, ColumnAudReg, OldValue, NewValue)
SELECT @OperationNum, ISNULL(CAST(INSERTED.ID as nvarchar),CAST(DELETED.ID as nvarchar)), 'USERNAME','A', CONVERT( VARCHAR(3500),DELETED.USERNAME), CONVERT( VARCHAR(3500),INSERTED.USERNAME)
FROM INSERTED FULL OUTER JOIN DELETED ON INSERTED.ID=DELETED.ID
END
GO

Answer 1

添加这个:

INSERT INTO AuditedRows (OperationNum, RowPK)
SELECT @OperationNum, ISNULL(CAST(INSERTED.ID as nvarchar),CAST(DELETED.ID as nvarchar))
FROM INSERTED FULL OUTER JOIN DELETED ON INSERTED.ID=DELETED.ID
-- Restrict it to only those where the username is changing from or to %_ess
WHERE (deleted.username like '%_ess' or inserted.username like '%_ess')
INSERT INTO AuditedRowsColumns (OperationNum, RowPK, ColumnName, ColumnAudReg, OldValue, NewValue)
SELECT @OperationNum, ISNULL(CAST(INSERTED.ID as nvarchar),CAST(DELETED.ID as nvarchar)), 'USERNAME','A', CONVERT( VARCHAR(3500),DELETED.USERNAME), CONVERT( VARCHAR(3500),INSERTED.USERNAME)
FROM INSERTED FULL OUTER JOIN DELETED ON INSERTED.ID=DELETED.ID
-- Restrict it to only those where the username is changing from or to %_ess
WHERE (deleted.username like '%_ess' or inserted.username like '%_ess')