SQL Server 2008 Audit trigger based on sub string

Question

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

Solution

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')

Comments

[Web-E]

PLease see this answer - askubuntu.com/a/151164/35775

[Taryn]

Have you tried to write the trigger? If so, then please post the code.

[Taryn]

When do you want to check the data on insert, on update? You need to provide more details.

[Geoff Dawdy]

I would like to check based on insert, delete, and/or update. I've added the current code as it stands.

[Web-E]

Thats dual GPU config in notebook (nvida optimus). Downloading nvidia driver will cause a non gui system

[upapilot]

What are you talking about? He says his Nvidia driver isnt functioning so he needs to download the Driver right?

[Web-E]

no, 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]

Thanks guy for reply, so I've to wait from Hvidia?

[upapilot]

Intel HD Graphics + Nvidia is not called dual graphics. Its just standard Dedicated + Integrated. Dual Graphics is like Nvidia + Nvidia/ATI + ATI etc.

[nilsonneto]

@upapilot - please edit your answer with a clarification between dual GPU and dual integrated graphics etc.

[Jeff Rosenberg]

You 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]

It 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]

There is no updated pseudo table. A deleted row appears in deleted but not in inserted. An inserted row appears in inserted but not in deleted. An updated row appears in both deleted and inserted.