Trigger - Pass field that was changed into Audit Table

I have built triggers for my tables but I didn't include a variable for the changed field so instead of below fields in trigger, CenterID , Center_Desc , I need to include a variable that specifies the affected field name and the changed value to capture changes from all tables; as

@Modified_Date datetime,
@Table_Name varchar (1000),
@ActionID int
@Affected_Field varchar (1000)
@Value varchar (1000)

ALTER Trigger [dbo].[tr_Delete_LU_Center_Site] On [dbo].[LU_Center_Site]
FOR Delete
AS
BEGIN
DECLARE
@Modified_Date datetime,
@Table_Name varchar (1000),
@ActionID int
 
SET @Modified_Date  = getdate()
SET @Table_Name = 'LU_Center_Site'
SET @ActionID = '3'
 
INSERT INTO tbl_Audit(ActionID, Modified_Date, Table_Name, UserId, CenterID, Center_Desc)
Select @ActionID,  @Modified_Date, @Table_Name, UserID, CenterID , Center_Desc from Deleted
END
Glen_DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dportasCommented:
If you can use Enterprise Edition then use the Change Data Capture feature instead of a trigger:
http://msdn.microsoft.com/en-us/library/bb522489.aspx

If you must roll your own then create audit tables that look like the source tables, with the right columns and data types. For data capture that's by far the most efficient and effective way in my experience.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glen_DAuthor Commented:
whoa...create an audit table for each prod table....??? sounds like overkill and defeats the purpose of the db...not knocking but truly wondering...

we're going to convert this to Oracle and I need the triggers...

thx
0
dportasCommented:
Triggers in Oracle are completely different.

In what way would creating tables for your audit data "defeat the purpose"? What are you trying to achieve? What is the structure of the tables you expect to store the captured data in? At the very least you'll need to reproduce one set of key columns per table but I thought you wanted to capture the other data as well.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

dportasCommented:
BTW, the Oracle equivalent of SQL Server CDC is "Flashback". I suggest you consider the built in features first rather than invent your own.
0
Glen_DAuthor Commented:
I checked Oracles auditing capabilities...one of the requirements is to have records of the old values and new values; Oracle can't provide this service...thx
0
Glen_DAuthor Commented:
Thx for all your help...if you can give me an answer on this, that would be great...

All I need on this trigger is a way to capture the UserID the front end is passing and get it into my audit tables (see sp below); if you could figure this out, I would give you 5000 pts if I could but would be very grateful none the less.  Where I'm putting the variable UserID is not working...my simple trigger I created is capturing the userid so I know it's coming in.

Thx

BEGIN
      DECLARE
            @IDENTITY_SAVE                  varchar(50),
            @AUDIT_LOG_TRANSACTION_ID      Int,
            @PRIM_KEY                  nvarchar(4000),
            @Inserted                      bit,
            --@TABLE_NAME                        nvarchar(4000),
             @ROWS_COUNT                        int,
             @UserID      int
 
      SET NOCOUNT ON

      --Set @TABLE_NAME = '[dbo].[LU_Center_Site]'
      Select @ROWS_COUNT=count(*) from inserted
      SET @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

      INSERT
      INTO [ERIC].dbo.AUDIT_LOG_TRANSACTIONS
      (
            TABLE_NAME,
            TABLE_SCHEMA,
            AUDIT_ACTION_ID,
            HOST_NAME,
            APP_NAME,
            MODIFIED_BY,
            MODIFIED_DATE,
            AFFECTED_ROWS,
            [DATABASE],
            UserID
      )
      values(
            'LU_Center_Site',
            'dbo',
            1,      --      ACTION ID For UPDATE
            CASE
              WHEN LEN(HOST_NAME()) < 1 THEN ' '
              ELSE HOST_NAME()
            END,
            CASE
              WHEN LEN(APP_NAME()) < 1 THEN ' '
              ELSE APP_NAME()
            END,
            SUSER_SNAME(),
            GETDATE(),
            @ROWS_COUNT,
            'ERIC',
            @UserID
            )
      
      Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()
      
      
      SET @Inserted = 0
      
      If UPDATE([CenterID])
      BEGIN
   
            INSERT
            INTO [ERIC].dbo.AUDIT_LOG_DATA
            (
                  AUDIT_LOG_TRANSACTION_ID,
                  PRIMARY_KEY_DATA,
                  COL_NAME,
                  OLD_VALUE_LONG,
                  NEW_VALUE_LONG,
                  DATA_TYPE
                  , KEY1
            )
            SELECT
                  @AUDIT_LOG_TRANSACTION_ID,
                convert(nvarchar(1500), IsNull('[CenterID]='+CONVERT(nvarchar(4000), IsNull(OLD.[CenterID], NEW.[CenterID]), 0), '[CenterID] Is Null')),
                'CenterID',
                  CONVERT(nvarchar(4000), OLD.[CenterID], 0),
                  CONVERT(nvarchar(4000), NEW.[CenterID], 0),
                  'A'
                  , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[CenterID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[CenterID], 0)))
                  
            FROM deleted OLD Full Outer Join inserted NEW On
                  (CONVERT(nvarchar(4000), NEW.[CenterID], 0)=CONVERT(nvarchar(4000), OLD.[CenterID], 0) or (NEW.[CenterID] Is Null and OLD.[CenterID] Is Null))
                  WHERE (
                  
                  
                        (
                              NEW.[CenterID] <>
                              OLD.[CenterID]
                        ) Or
                  
                        (
                              NEW.[CenterID] Is Null And
                              OLD.[CenterID] Is Not Null
                        ) Or
                        (
                              NEW.[CenterID] Is Not Null And
                              OLD.[CenterID] Is Null
                        )
                        )
       
            SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
      END
      
      If UPDATE([Center_Desc])
      BEGIN
   
            INSERT
            INTO [ERIC].dbo.AUDIT_LOG_DATA
            (
                  AUDIT_LOG_TRANSACTION_ID,
                  PRIMARY_KEY_DATA,
                  COL_NAME,
                  OLD_VALUE_LONG,
                  NEW_VALUE_LONG,
                  DATA_TYPE
                  , KEY1
            )
            SELECT
                  @AUDIT_LOG_TRANSACTION_ID,
                convert(nvarchar(1500), IsNull('[CenterID]='+CONVERT(nvarchar(4000), IsNull(OLD.[CenterID], NEW.[CenterID]), 0), '[CenterID] Is Null')),
                'Center_Desc',
                  CONVERT(nvarchar(4000), OLD.[Center_Desc], 0),
                  CONVERT(nvarchar(4000), NEW.[Center_Desc], 0),
                  'A'
                  , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[CenterID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[CenterID], 0)))
                  
            FROM deleted OLD Inner Join inserted NEW On
                  (CONVERT(nvarchar(4000), NEW.[CenterID], 0)=CONVERT(nvarchar(4000), OLD.[CenterID], 0) or (NEW.[CenterID] Is Null and OLD.[CenterID] Is Null))
                  where (
                  
                  
                        (
                              NEW.[Center_Desc] <>
                              OLD.[Center_Desc]
                        ) Or
                  
                        (
                              NEW.[Center_Desc] Is Null And
                              OLD.[Center_Desc] Is Not Null
                        ) Or
                        (
                              NEW.[Center_Desc] Is Not Null And
                              OLD.[Center_Desc] Is Null
                        )
                        )
       
            SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
      END
      
      If UPDATE([Center_Name])
      BEGIN
   
            INSERT
            INTO [ERIC].dbo.AUDIT_LOG_DATA
            (
                  AUDIT_LOG_TRANSACTION_ID,
                  PRIMARY_KEY_DATA,
                  COL_NAME,
                  OLD_VALUE_LONG,
                  NEW_VALUE_LONG,
                  DATA_TYPE
                  , KEY1
            )
            SELECT
                  @AUDIT_LOG_TRANSACTION_ID,
                convert(nvarchar(1500), IsNull('[CenterID]='+CONVERT(nvarchar(4000), IsNull(OLD.[CenterID], NEW.[CenterID]), 0), '[CenterID] Is Null')),
                'Center_Name',
                  CONVERT(nvarchar(4000), OLD.[Center_Name], 0),
                  CONVERT(nvarchar(4000), NEW.[Center_Name], 0),
                  'A'
                  , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[CenterID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[CenterID], 0)))
                  
            FROM deleted OLD Inner Join inserted NEW On
                  (CONVERT(nvarchar(4000), NEW.[CenterID], 0)=CONVERT(nvarchar(4000), OLD.[CenterID], 0) or (NEW.[CenterID] Is Null and OLD.[CenterID] Is Null))
                  where (
                  
                  
                        (
                              NEW.[Center_Name] <>
                              OLD.[Center_Name]
                        ) Or
                  
                        (
                              NEW.[Center_Name] Is Null And
                              OLD.[Center_Name] Is Not Null
                        ) Or
                        (
                              NEW.[Center_Name] Is Not Null And
                              OLD.[Center_Name] Is Null
                        )
                        )
       
            SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
      END
      
      -- Watch
      
      -- Lookup
      
      IF @Inserted = 0
      BEGIN
            DELETE FROM [ERIC].dbo.AUDIT_LOG_TRANSACTIONS WHERE AUDIT_LOG_TRANSACTION_ID = @AUDIT_LOG_TRANSACTION_ID
      END
      -- Restore @@IDENTITY Value  
    DECLARE @maxprec AS varchar(2)
    SET @maxprec=CAST(@@MAX_PRECISION as varchar(2))
    EXEC('SELECT IDENTITY(decimal('+@maxprec+',0),'+@IDENTITY_SAVE+',1) id INTO #tmp')
End

GO
EXEC sp_settriggerorder @triggername=N'[dbo].[tr_u_AUDIT_LU_Center_Site]', @order=N'Last', @stmttype=N'UPDATE'
0
dportasCommented:
You can use the SYSTEM_USER function in a trigger to get the current login name. You cannot pass parameters into a trigger.
0
Glen_DAuthor Commented:
i am getting the userid on this sp...

ALTER Trigger [dbo].[tr_Update_LU_Center_Site] On [dbo].[LU_Center_Site]
AFTER Update
AS
BEGIN
DECLARE
@Modified_Date datetime,
@Table_Name varchar (1000),
@ActionID int

SET @Modified_Date  = getdate()
SET @Table_Name = 'LU_Center_Site'
SET @ActionID = '1'

INSERT INTO tbl_Audit(ActionID, Modified_Date, Table_Name, UserId, CenterID, Center_Desc, COL_NAME)
Select @ActionID,  @Modified_Date, @Table_Name, UserID, CenterID , Center_Desc, COL_NAME from Inserted
END
0
Chris LuttrellSenior Database ArchitectCommented:
In that last trigger you are using the system psudo column UserID not a variable @UserID like you have in the large trigger above:
   DECLARE
            @IDENTITY_SAVE                  varchar(50),
            @AUDIT_LOG_TRANSACTION_ID      Int,
            @PRIM_KEY                  nvarchar(4000),
            @Inserted                      bit,
            --@TABLE_NAME                        nvarchar(4000),
             @ROWS_COUNT                        int,
             @UserID      int    ----------------------------<<<<<<<<<<<<<<<<<<  do not need this if you want system UserID
 
      SET NOCOUNT ON

      --Set @TABLE_NAME = '[dbo].[LU_Center_Site]'
      Select @ROWS_COUNT=count(*) from inserted
      SET @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

      INSERT
      INTO [ERIC].dbo.AUDIT_LOG_TRANSACTIONS
      (
            TABLE_NAME,
            TABLE_SCHEMA,
            AUDIT_ACTION_ID,
            HOST_NAME,
            APP_NAME,
            MODIFIED_BY,
            MODIFIED_DATE,
            AFFECTED_ROWS,
            [DATABASE],
            UserID
      )
      values(
            'LU_Center_Site',
            'dbo',
            1,      --      ACTION ID For UPDATE
            CASE
              WHEN LEN(HOST_NAME()) < 1 THEN ' '
              ELSE HOST_NAME()
            END,
            CASE
              WHEN LEN(APP_NAME()) < 1 THEN ' '
              ELSE APP_NAME()
            END,
            SUSER_SNAME(),
            GETDATE(),
            @ROWS_COUNT,
            'ERIC',
            @UserID    -------------------------------------------------<<<<<<<<<<<<<<<<   get rid of the @ here to use psudo column, same for all other references
            )
--- rest of code...
0
Glen_DAuthor Commented:
ok...no pro but again, how do I capture the userID coming in from the front end...here's the code for that

this line has defaulted (stubbed) to a 1 just to test...thx

<cfprocparam type=in  cfsqltype=cf_sql_numeric value=1>

full code here

<cffunction access=public name=editCenter returntype=boolean>
  <cfargument name=centerID type=numeric required=yes>
  <cfargument name=centerName type=string required=yes>
  <cfstoredproc datasource=#dataSourceName# procedure=usp_editCenter>
    <cfprocparam type=in  cfsqltype=cf_sql_numeric value=1>
    <cfprocparam type=in  cfsqltype=cf_sql_numeric value=#centerID#>
    <cfprocparam type=in  cfsqltype=cf_sql_varchar value=#centerName#>
    <cfprocparam type=out cfsqltype=cf_sql_bit     variable=centerEdited>
  </cfstoredproc>
  <cfset returnValue= true>
  <cfif #centerEdited# EQ 0>
    <cfset returnValue = false>
  </cfif>
  <cfreturn returnValue>
</cffunction>
0
Glen_DAuthor Commented:
we have the userids loaded in a table...the system userid (db id) is a composed of one account so we need the session userid from the table...thx
0
Anthony PerkinsCommented:
I hate to deflate your bubble, but you do realize that If UPDATE() does not necessarily mean that the the column value has actually changed, right?
0
Glen_DAuthor Commented:
I completed some research on this and this looks like it might work...CONTEXT_INFO...anyone have any guidance on this?  Thx

DECLARE @BinVar varbinary(128)
SET @BinVar = CAST(N'User Name' AS varbinary(128) )
SET CONTEXT_INFO @BinVar

This way, you can easily map your ASP.Net user to corresponding connections.
In the audit trigger, this user name can be obtained from its context by the following query:

SELECT CAST(CONTEXT_INFO() AS NVARCHAR(64) )
0
Chris LuttrellSenior Database ArchitectCommented:
That could work, if you set the context in the sp and use CONTEXT_INFO() in your trigger.  I just put the code below in a trigger and it works when I put a name in context at the top of the sp.
    DECLARE @myUserId NVARCHAR(64)
    SET @myUserId = CAST(CONTEXT_INFO() AS NVARCHAR(64) )
    INSERT INTO T3
    SELECT MyId, @myUserId
    FROM Inserted

Open in new window

0
Anthony PerkinsCommented:
Would it not be easier setting the user name when connecting to the database, rather then huntng down all the UPDATE statements? or perhaps I am missing something.
0
Glen_DAuthor Commented:
we rolled our own...thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.