Best practice is to keep the scope of a trigger as small as possible.
All executed by a trigger is in the same transaction as the original query (insert/update/delete) !
Meaning, if the sproc fails, the trigger fails, the insert/update/delete fails !
OR: use stored procedures to manipulate your data and then just incorporate your wanted proc in that procedure.
OR: just insert the key values and the parameters you need in another "trigger-worker-table" and have a job take care of running the sproc, starting from this trigger-worker-table.
you can even launch the job ad hoc using this method : http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
Don't launch the job from within the trigger using sp_startjob, because that will need security opened up way to much !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me