SELECT INTO ERROR. Using a variable.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

joss22 - 29 Apr 2008 07:48 GMT

I have build a trigger for insert, update and delete that works with
all the fields of a table, it doesn't matter the names and the number
of fields.
As triggers can't manage text, ntext or image fields I get the list of
the other fields names in the variable @myList.
Then my trigger goes:

select @mySqlins = 'select ' + @myList+ ' into #ins FROM inserted'
exec (@miSqlins)

It doesn't raise any error but the trigger doesn't work properly. It
cancels the updates in my table!

If I change that code for thi below:

SELECT @myList into #ins FROM inserted

When I check the syntax it raises something like: You haven't
specified any column for the column 1 of '#ins'

Any help, please?

Reply to this Message

Uri Dimant - 29 Apr 2008 08:19 GMT

joss22
When you build @myList , do you return SUM().. or sopme another
aggregation result?

SELECT SUM(col) AS mycolname

BTW, be aware of SQL Injections while you build dynamic sql in that way

>I have build a trigger for insert, update and delete that works with
> all the fields of a table, it doesn't matter the names and the number
[quoted text clipped - 17 lines]
>
> Any help, please?

Reply to this Message

joss22 - 29 Apr 2008 08:43 GMT

Nop. I'm using this procedure to load this variable:

********* PROCEDURE ***********
CREATE PROCEDURE getColumnList @table varchar(255), @columns
varchar(1000) OUTPUT
AS

DECLARE @CONTENIDO AS VARCHAR(250)

set @contenido=''

SELECT @content=@content+column_name+','
FROM [MYBD].[INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_name = @table and data_type not in
('ntext','text','image')

select @columnas = SUBSTRING(@contenido,1,LEN(@contenido)-1)
GO

********* END PROCEDURE ***********

And then I use it on my trigger this way:

exec getColumnList 'MYTABLE' , @listaCampos output

and @listaCampos is 'field1,field2,field3,field4'

> joss22
> When you build @myList , do you return SUM().. or sopme another
[quoted text clipped - 25 lines]
>
> > Any help, please?

Reply to this Message

Roy Harvey (SQL Server MVP) - 29 Apr 2008 12:27 GMT

EXEC of dynamic SQL creates a new context that does not have access to
the INSERTED or DELETED tables. Only code written directly into the
trigger can see those tables.

Roy Harvey
Beacon Falls, CT

>I have build a trigger for insert, update and delete that works with
>all the fields of a table, it doesn't matter the names and the number
[quoted text clipped - 17 lines]
>
>Any help, please?

Reply to this Message

joss22 - 30 Apr 2008 08:44 GMT

Thanks Ron. I've solved the problem just changing those text and ntext
fields to varchar(MAX) and nvarchar(max)

On 29 abr, 13:27, "Roy Harvey (SQL Server MVP)" <roy_har...@snet.net>
wrote:

> EXEC of dynamic SQL creates a new context that does not have access to
> the INSERTED or DELETED tables. Only code written directly into the
[quoted text clipped - 24 lines]
>
> >Any help, please?

Reply to this Message