Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 Audit trigger based on sub string
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 Audit trigger based on sub string
    primarykey
    data
    text
    <p>I would like to create a trigger based on a column but only for those records that end in <code>_ess</code>. How can I set up an audit trigger to do this?</p> <p>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.</p> <pre><code>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&gt;0 BEGIN EXECUTE sp_getbindtoken @DBMSTransaction OUTPUT END ELSE BEGIN SET @DBMSTransaction = NULL END IF PatIndex( '%\%',SUSER_SNAME()) &gt; 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) &gt; 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)&gt;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)&gt;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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGeoff Dawdy
    1. USGeoff Dawdy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou 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.
      singulars
      1. POSQL Server 2008 Audit trigger based on sub string
      1. USJeff Rosenberg
    2. COHave you tried to write the trigger? If so, then please post the code.
      singulars
      1. POSQL Server 2008 Audit trigger based on sub string
      1. USTaryn
    3. COWhen do you want to check the data on insert, on update? You need to provide more details.
      singulars
      1. POSQL Server 2008 Audit trigger based on sub string
      1. USTaryn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload