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')
Share:
478

Related videos on Youtube

Geoff Dawdy
Author by

Geoff Dawdy

Updated on November 23, 2022

Comments

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