Home Forums SQL Server 2005 Development Main Store procedure to execute several store procedures or Trigger? RE: Main Store procedure to execute several store procedures or Trigger?

  • Well, it really depends on how complicated your data is and what kind of work your triggers do.

    I, personally, have been avoiding triggers in the past 12 years. I like to keep my code and logic plain simple and "procedural"... It might look somewhat crippled, but as a tech-leader I have the luxury of choosing between readability and sophistication level of a solution.

    As for your question: If you DO have triggers already - go ahead and use them, as otherwise you'll bump into more problems than you might think of.

    If you do not have any triggers yet, I recommend you split your code into several SPs and call them from one main SP. It is best solution because:

    1. Code is broken into logoically different pieces in an elegant way

    2. You might wanna change one SP without changing tons of lines of code.

    3. All the reasons for using procedural coding apply here... event the name 'procedural' is a good hint

    If I use One Main sp to execute all the child sp's, do I need to call ALL the parameters of the child sp's in the main sp?

    Just like any other SP, you may or may not pass all parameters. You may leave NULLs or DEFAULT keyword, if they apply

    If I use a trigger to execute a store procedure? How do I configure the parameters

    In order to do that you should learn, first, about what INSERTED and DELETED logical tables are and take your parameters from there, but then again - It's a trigger based solution, even if a trigger fires SP...