1

I have a generic CLR trigger that I use for auditing. This trigger is created for each and every auditable table in the database.

The first statement that the trigger executes is to get the table it's associated with using sys.dm_tran_locks. But this is not always working as expected.

I have one common audit table in the database, with the following structure:

CREATE TABLE [dbo].[audit](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user_name] [varchar](50) NULL,
    [date_time] [datetime] NULL DEFAULT (getdate()),
    [item_table] [varchar](50) NULL,
    [item_id] [int] NULL,
    [item_action] [int] NULL,
    [description] [text] NULL)

Each row in the audit table denotes a change to a record in a table, and the description table contains all the columns that have been affected, and in this format:

 column1name|oldvalue|tovalue-column2name|oldvalue|tovalue

Note: the | and - separators are for display purposes in the above example.

Each auditable table has a trigger associated with it:

ALTER TRIGGER [dbo].[tr_table1_audit] ON [dbo].[table1] 
WITH EXECUTE AS CALLER AFTER  INSERT, DELETE, UPDATE 
AS 
EXTERNAL NAME [Triggers].[Audit].[InsertRecord]

The CLR trigger is as follows:

public class Audit
{
    [SqlTrigger]
    public static void InsertRecord()
    {
        //1. Get triggers associated table name (not working correctly)

        //2. Get affected columns
        switch (triggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                 using (SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT * FROM INSERTED", conn))
                 using (DataTable inserted = new DataTable())
                 {
                    adapter.Fill(inserted);

                    //1. Loop through inserted rows
                    //2. For each row, loop through columns
                    //3. Build string for the description column above
                    //4. Insert audit record 
                 }
                 break;

            //Update and delete here...
        }
    }
}

So I was thinking of moving the code from the trigger to a stored procedure and passing the table name as a parameter.

Is it possible to use the inserted and deleted tables in a stored procedure?

Improve this question
6
  • 3
    the OUTPUT clause as access to INSERTED and DELETED Commented Mar 10, 2015 at 13:12
  • 1
    Normally when I do this I implement this feature as a generator that generates a set of per-table audit triggers based on the table metadata from the system data dictionary. You might be better off with that approach then trying to introspect transaction metadata at runtime. Commented Mar 10, 2015 at 16:36
  • @ConcernedOfTunbridgeWells What do you mean by generating triggers based on table metadata? Commented Mar 11, 2015 at 7:06
  • 1
    @Ivan-Mark Debono You write a code generator system that reads the name and columns for each table from the system data dictionary. This information is used to generate the creation script for a trigger for each table you want to implement the audit functionality for. The output of the system is a DDL script that creates triggers for each table with the correct columns for the table that it has read from the data dictionary. You can also generate DDL for audit tables as well. Commented Mar 11, 2015 at 11:57
  • 1
    I agree with ConcernedOfTunbridgeWells, trigger and audit table generators is a very good approach. I wrote one (only 147 lines of code) using Visual Studio T4 templates geeks.ms/blogs/sqlranger/archive/2013/10/16/…. The article in in Spanish, but code is code. Commented Mar 11, 2015 at 13:06

2 Answers 2

Reset to default
1

First, never, under any circumstances, do auditing from anything except a trigger. You will miss some changes to the data otherwise.

Next, why are you using a CLR? There is nothing in what that does that can't be done using ordinary code. (And BTW, never, ever, ever loop through records in a trigger, learn to use set-based operations)

Next. It is a very poor idea to have only one audit table, that will become a hotspot in the database and may cause performance issues. You should have one audit table per table audited.

A better idea is to create the code to create the new audit table(s) and the audit triggers when ever a new table is added. All our audit triggers are expactly the same. This is the code generator system that @ConcernedOfTunbridgeWells was discussing in the comments.

Further, your current audit structure make it hard to find which other records might have been affected by the same action. This is critical when you need to see all the records impacted by a bad update or allteh records deleted by a malicious user. Our audit system has two tables for each table. One that records the date and time of the action and a related table that includs the data about all the records changed in that action. Since you coudl potentially change millions of records in one update, it is critical to be able to see what else was affected.

Now to answer your question, yes you can access the inserted and deleted pseudotables outside a trigger. You do it using the OUTPUT clause. However, in your particular case, I think this would be a very poor idea for reasons. discussed above.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I agree with all you said. Unfortunately I need to keep the same table structure. I'm using CLR because it's faster to loop through records and columns. I couldn't find a better way to do it directly in T-SQL.
If you use the techique of building a code generator, you no longer need to loop in the trigger.
I'm going for this solution because it's the only that will work for sure.
0

The answer to your actual question is no. It is not possible to pass INSERTED and DELETED objects up as parameters to a stored procedure. At best you could store the data they hold into XML typed variables and pass up these, but that won't scale well.

You'd be best holding up to your current trigger strategy and pass the table's name as a parameter to your CLR method. Likely you have an entry point method with a decorating SqlTriggerAttribute declared for each table you want to audit? If so then simply use the same value you use as the attribute's Target property as a parameter to your generic method.

Improve this answer

1 Comment

@Ivan-MarkDebono I've updated my answer with additional thoughts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.