insert trigger

[Saama5]

whole idea of giving this code is there I want to use in exec command inserted whereas when I use this way it says inserted is not object

CREATE TRIGGER tr1 ON [dbo].[Table1]
FOR INSERT
AS
declare @temp nvarchar(175)
declare @tbl nvarchar(15)
select @tbl ='Emp'
declare @rno int
declare @xx int
select @rno =recordnumber from inserted
select @xx =recordnumber from @tbl where name='abc'
declare @xx1 nvarchar(5)
declare @rno1 nvarchar(5)
select @xx1 =convert(nvarchar(5),xx1)
select @rno1=convert(nvarchar(5),rno1)
select @temp = 'select convert(nvarchar(5),level) from '+@tbl +' join inserted on '+@xx1+' = '+@rno1
EXEC( @temp)

 

[rt63]

My guess is that inserted as on object is available only in the scope of the trigger. When you are giving a command like execute(@sql), it basically executes a system stored procedure sp_execute and I think it works only on user defined tables.

From what I can see, you do not really need to build a dynamic SQL to run thru execute but if this is just a sample and the real requirements are different, you can do the following :

1. Create a temporary table and store the contents of inserted in it


select *
into #temp_UrTable
from inserted


2. Use this table in your exec statement


select @temp = 'select convert(nvarchar(5),level) from '+@tbl +' join #temp_UrTable on '+@xx1+' = '+@rno1
EXEC( @temp)


Hope it helps

RT