title: Is it possible to use inserted and deleted tables in stored procedures?
body: <p>I have a generic CLR trigger that I use for auditing. This trigger is created for each and every auditable table in the database.</p>
  
  <p>The first statement that the trigger executes is to get the table it's associated with using <code>sys.dm_tran_locks</code>. But this is not always working as expected.</p>
  
  <p>I have one common audit table in the database, with the following structure:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Each row in the audit table denotes a change to a record in a table, and the <code>description</code> table contains all the columns that have been affected, and in this format:</p>
  
  <pre><code> column1name|oldvalue|tovalue-column2name|oldvalue|tovalue
  </code></pre>
  
  <p>Note: the <code>|</code> and <code>-</code> separators are for display purposes in the above example.</p>
  
  <p>Each auditable table has a trigger associated with it:</p>
  
  <pre><code>ALTER TRIGGER [dbo].[tr_table1_audit] ON [dbo].[table1] 
  WITH EXECUTE AS CALLER AFTER  INSERT, DELETE, UPDATE 
  AS 
  EXTERNAL NAME [Triggers].[Audit].[InsertRecord]
  </code></pre>
  
  <p>The CLR trigger is as follows:</p>
  
  <pre><code>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...
          }
      }
  }
  </code></pre>
  
  <p>So I was thinking of moving the code from the trigger to a stored procedure and passing the table name as a parameter.</p>
  
  <p>Is it possible to use the inserted and deleted tables in a stored procedure?</p>