SQL Server 2008 Audit trigger based on sub string
478
Just add this:
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')
Related videos on Youtube

Author by
Geoff Dawdy
Updated on November 23, 2022Comments
-
Geoff Dawdy about 3 hours
I would like to create a trigger based on a column but only for those records that end in
_ess
. How can I set up an audit trigger to do this?Here is the current trigger but it just checks for all changes to username, whereas I just want it to check when username is updated to or from a username ending in _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 [email protected]@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 [email protected]@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
-
Web-E over 10 yearsPLease see this answer - askubuntu.com/a/151164/35775
-
Taryn over 9 yearsHave you tried to write the trigger? If so, then please post the code.
-
Taryn over 9 yearsWhen do you want to check the data on insert, on update? You need to provide more details.
-
Geoff Dawdy over 9 yearsI would like to check based on insert, delete, and/or update. I've added the current code as it stands.
-
-
Web-E over 10 yearsThats dual GPU config in notebook (nvida optimus). Downloading nvidia driver will cause a non gui system
-
upapilot over 10 yearsWhat are you talking about? He says his Nvidia driver isnt functioning so he needs to download the Driver right?
-
Web-E over 10 yearsno, he mentioned he has a dual graphics card (read the question again). Search with the notebook model. It will say it has optimus. Now optimus is not supported by nvidia in linux and installing nvidia driver will give black screen.
-
Fulvio Rogantin over 10 yearsThanks guy for reply, so I've to wait from Hvidia?
-
upapilot over 10 yearsIntel HD Graphics + Nvidia is not called dual graphics. Its just standard Dedicated + Integrated. Dual Graphics is like Nvidia + Nvidia/ATI + ATI etc.
-
nilsonneto over 10 years@upapilot - please edit your answer with a clarification between dual GPU and dual integrated graphics etc.
-
Jeff Rosenberg over 9 yearsYou can't set the trigger to only run for records that end in "_ess", but you can simply add a
WHERE
clause to any DML statements in your trigger. -
Geoff Dawdy over 9 yearsIt looks like this only checks for deleted or inserted usernames. If I add "or updated.username like '%_ess'" I get the error "The multi-part identifier "updated.username" cound not be bound"
-
Ben over 9 yearsThere is no
updated
pseudo table. A deleted row appears indeleted
but not ininserted
. An inserted row appears ininserted
but not indeleted
. An updated row appears in bothdeleted
andinserted
.