Table variable not working in dynamic SQL

Question

My trigger is as below ,

Alter TRIGGER [dbo].[LogTable_InsertTrigger] on [dbo].[std_table]  AFTER  INSERT
as

    DECLARE  @ColName varchar(50), @QueryText nvarchar(max)

    declare @inserted  TABLE(
      [CountryID] [int] NOT NULL,
      [Country] [nvarchar](255) NOT NULL,
      [RegionId] [int] NULL
    )
    insert into @inserted
    select * from inserted

    DECLARE objCursor CURSOR FAST_FORWARD FOR
    select  ColName from dbo.getColumnNames('std_table')  

    OPEN objCursor 
    FETCH NEXT FROM objCursor INTO @ColName
    WHILE @@FETCH_STATUS = 0
    BEGIN
      set @QueryText= '
         insert into dbo.LogTable
         (StandardType,Attribute,Action,NEwValue,UserId,ModifiedDate)
        select ''Country'','''+@ColName+''',''Insert'','+@ColName+',1,getdate()
        from @inserted'

       EXEC sp_executesql   @QueryText  

       FETCH NEXT FROM objCursor INTO @ColName
    END
    CLOSE objCursor
    DEALLOCATE objCursor

When I try to insert to table std_table in DA Layer I get the exception Must declare the table variable "@inserted".

I couldn't use the inserted table directly because I am writing a dynamic query, inside which magic tables wont work. So I am trying to dump the data in inserted table to a temp table and to access from that.

I tried with

select * 
into #inserted
from inserted

This works, but since my application is accessed by many users over network this will cause data issues. So I can't use this.

cursor in a trigger: you are going to TSQL hell!

Mitch Wheat
– Mitch Wheat

2012-02-22 03:55:02 +00:00
Commented Feb 22, 2012 at 3:55 — Mitch Wheat
– Mitch Wheat, Commented Feb 22, 2012 at 3:55

Aaron Bertrand · Accepted Answer · 2012-02-22 05:03:57Z

1

Several things wrong here.

That is a table variable, not a user defined table type.
If #temp tables work, why do you think that will cause data issues for multiple users? Each user will get their own version of the #temp table.
If you know there are exactly three columns and you can hard-code the table variable declaration, why do you need to then generate the three sets of inserts dynamically? Aren't the column names CountryID,Country,RegionID?

If you really need to do this dynamically then it seems like you could do this an easier way without an explicit cursor - not that this is necessarily a bad thing or that it will perform any worse than the below, but the cursor is just much more verbose and ugly code:

ALTER TRIGGER [dbo].[LogTable_InsertTrigger] 
ON [dbo].[std_table]  
AFTER  INSERT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT * INTO #t FROM inserted;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'';

  SELECT @sql = @sql + CHAR(13) + CHAR(10) + N'INSERT INTO dbo.LogTable
        (StandardType,Attribute,Action,NewValue,UserId,ModifiedDate)
        SELECT ''Country'','''+ColName+''',''Insert'','+ColName+',1,GETDATE() 
     FROM #t;'
  FROM dbo.GetColumnNames('std_table');

  EXEC sp_executesql @sql;
END
GO

edited Feb 22, 2012 at 5:03

answered Feb 22, 2012 at 3:41

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

8 Comments

Kuntady Nithesh Over a year ago

3. My tables has more than three columns . and this same query will repeat for 20 tables which all together will have around 200 + columns

Aaron Bertrand Over a year ago

Well that sounds like a design disaster, but the above trigger should work for any number of columns (assuming the function is accurate and all of those columns actually do exist in inserted). You might consider change tracking or creating a shadow table that matches the primary table so that you can perform 1 insert instead of 200.

Kuntady Nithesh Over a year ago

I coudn't do like wat you said . I get error as "Invalid object name 'inserted'"

Aaron Bertrand Over a year ago

Hmm, appears like a scoping issue. How often does the schema for std_table change?

Aaron Bertrand Over a year ago

You don't have to explicitly drop #temp tables (but you can if you wish after exec sp_executesql), since they automatically go out of scope at the end of execution. The reason you might not want to drop it is because the metadata can be reused in tempdb. I haven't proven or disproved this, but if it is true, you'll want to use a more distinct name than #t in each separate trigger, e.g. #std_table.

|

Collectives™ on Stack Overflow

Table variable not working in dynamic SQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related