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 over 1 year
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 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
-
Web-E almost 12 yearsPLease see this answer - askubuntu.com/a/151164/35775
-
Taryn about 11 yearsHave you tried to write the trigger? If so, then please post the code.
-
Taryn about 11 yearsWhen do you want to check the data on insert, on update? You need to provide more details.
-
Geoff Dawdy about 11 yearsI would like to check based on insert, delete, and/or update. I've added the current code as it stands.
-
-
Web-E almost 12 yearsThats dual GPU config in notebook (nvida optimus). Downloading nvidia driver will cause a non gui system
-
upapilot almost 12 yearsWhat are you talking about? He says his Nvidia driver isnt functioning so he needs to download the Driver right?
-
Web-E almost 12 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 almost 12 yearsThanks guy for reply, so I've to wait from Hvidia?
-
upapilot almost 12 yearsIntel HD Graphics + Nvidia is not called dual graphics. Its just standard Dedicated + Integrated. Dual Graphics is like Nvidia + Nvidia/ATI + ATI etc.
-
nilsonneto almost 12 years@upapilot - please edit your answer with a clarification between dual GPU and dual integrated graphics etc.
-
Jeff Rosenberg about 11 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 about 11 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 about 11 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
.