exec scope

Question

exec scope

nadersam

SSCertifiable

Points: 6542
More actions
November 28, 2010 at 5:38 am

#227210

Hi All,
I am trying to write a trigger that dynamically creates an audit trail for data change, whether it's Insert,Update or delete.
To make it work dynamically i use the exec command and pass the dynamic sql to it.
This dynamic sql uses the inserted keyword to read the new inserted or updated record.
It seems the exec command has a different scope as when i run the insert on table, i get an error :Invalid object name inserted.
Can anyone help please.
Thanks in advance
Nader

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The inserted and deleted tables are only visible within the trigger, not within anything it calls (including dynamic SQL)

I would strongly advise you against dynamic triggers. They look nice, but they're hard to write (because of the visibility of the inserted and deleted tables) and when you start building up SQL statements, using temp tables and all that, it tends to make the triggers run longer than necessary - longer locks, more blocking, deadlocks, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nadersam SSCertifiable Points: 6542 More actions · Answer 2

Thanks Gail,

I appreciate your reply.

But that means i have to rewrite the trigger code again 🙁 and so far i don't have other ideas on how to rewrite it.

Thanks

Nader

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Well I don't know what you're doing, so I can't really advise you...

If you're trying to use dynamic SQL so that you can use the same trigger code on every table, don't. Write a proc/script that, using dynamic SQL, creates a trigger specifically for each table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

nadersam (11/28/2010)
Thanks Gail,
I appreciate your reply.
But that means i have to rewrite the trigger code again 🙁 and so far i don't have other ideas on how to rewrite it.
Thanks
Nader

I agree with Gail. Even if it means rewritting it again, dynamic SQL in a trigger is a bad idea especially from a performance point of view. A lot of people try to make a "generic" bit of trigger code that would be identical for all tables seemingly making life easier but it just ends up making for some real performance problems.

If you have a lot of tables to build audit triggers for, you'd be better off writing some code that will build static trigger code for a given table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nadersam SSCertifiable Points: 6542 More actions · Answer 5

Hi Gail,

What you said is exactly what i need to do, i need to write just a single block of code and reuse it for all tables.

According to our previous discussion that's not a good idea in terms of performance, so i am going to rewrite all tables triggers again to be specific for each table.

Thanks for your advice.

Nader

nadersam SSCertifiable Points: 6542 More actions · Answer 6

Thanks Jeff,

That's exactly what i will start working on in the next period.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

nadersam (11/29/2010)
Thanks Jeff,
That's exactly what i will start working on in the next period.

Thanks for the feedback, nadersam.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)