Dynamic use of "inserted" and "deleted" in a trigger

Hello,
Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger?
I get an "invalid object name" with these statement.
set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
EXEC sp_executesql @.sqlcmd
Any other suggestions?
Thanks!
Per>'insert mydatabase.dbo.mytable select * from inserted'
what is dynamic here? No need, If i understand correctly
TRY THIS
INSERT INTO mydatabase.dbo.mytable
SELECT <COLUMN LIST AS IN my table order> from inserted
if schema is different or identity is in the mytables, it throws an error.
if you are expecting something else, do post
Regards
R.D
"Per Buus S?rensen" wrote:

> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger
?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||Sorry for the bad example, it should have been:
set @.sqlcmd = 'insert ' + @.db +'.dbo.' + @.table + ' select * from inserted'
EXEC sp_executesql @.sqlcmd
Per
"R.D" wrote:
> what is dynamic here? No need, If i understand correctly
> TRY THIS
> INSERT INTO mydatabase.dbo.mytable
> SELECT <COLUMN LIST AS IN my table order> from inserted
> if schema is different or identity is in the mytables, it throws an error.
> if you are expecting something else, do post
> Regards
> R.D
>
> "Per Buus S?rensen" wrote:
>|||Hi
No you cannot
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a
> trigger?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
as short, concise and efficient as possible because they run in a
transaction.
I find that the best way to create generic trigger code is to generate
it semi-automatically at design-time using the information_schema
views. This is very easy to do as long as the trigger code is identical
or similar in each case. If you prefer you can put your common code in
a stored proc and then insert the contents of the Inserted / Deleted
tables into local temporary table(s) that the proc can use.
Hope this helps.
David Portas
SQL Server MVP
--|||As Portas said, you have to use intermediate temp table because each exec
will have its own scope and inserted table is not in exec() scope.
well if you stick to using dynamic sql for peculiar reasons
use this
create table ##mytemptable<data definition same as inserted table)
insert into ##mytemptable select * from inserted
exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
drop table ##mytemptable
--
if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put N'
before string.
Regards
R.D
"Uri Dimant" wrote:

> Hi
> No you cannot
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
>
>|||The "temp" solution was my first through about a work around, however I dont
know the definition of the source table, since users can change layout from
ERP system, so the trigger should be as general as possiple.
Is it possiple easierly to create a copy of the source table definition as a
temp table?
Per
"R.D" wrote:
> As Portas said, you have to use intermediate temp table because each exec
> will have its own scope and inserted table is not in exec() scope.
> well if you stick to using dynamic sql for peculiar reasons
> use this
> create table ##mytemptable<data definition same as inserted table)
> insert into ##mytemptable select * from inserted
> exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
> drop table ##mytemptable
> --
> if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put
N'
> before string.
> Regards
> R.D
> "Uri Dimant" wrote:
>|||I am aware of the performance issue about such a trigger, but it will be use
d
on smaller master data tables, which updates x other similar tables in other
databases.
Users can setup new databases them self, so flexibility is more important
than performance.
Per
"David Portas" wrote:

> No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
> as short, concise and efficient as possible because they run in a
> transaction.
> I find that the best way to create generic trigger code is to generate
> it semi-automatically at design-time using the information_schema
> views. This is very easy to do as long as the trigger code is identical
> or similar in each case. If you prefer you can put your common code in
> a stored proc and then insert the contents of the Inserted / Deleted
> tables into local temporary table(s) that the proc can use.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Like I suggested before, it sounds like code generation is the way to
go. Much simpler to maintain than dynamic code.
David Portas
SQL Server MVP
--|||I see that the server creates the temp table with correct definition, if it
doesn't exists.
So that solves my problem...so far.
Per
"Per Buus S?rensen" wrote:
> The "temp" solution was my first through about a work around, however I do
nt
> know the definition of the source table, since users can change layout fro
m
> ERP system, so the trigger should be as general as possiple.
> Is it possiple easierly to create a copy of the source table definition as
a
> temp table?
> Per
> "R.D" wrote:
>