Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Main Store procedure to execute several store procedures or Trigger?


Main Store procedure to execute several store procedures or Trigger?

Author
Message
Padma-1078840
Padma-1078840
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 147
I have a main table Broker which has an identity field as primary key, once inserted, is supposed to insert relevant data on several other tables by accessing the @@Identity. I have created store procedures for all the other tables.
Initially I was planning on just creating triggers to insert data in the child tables, now I am not sure if I should use a trigger to execute a store procedure, or a Main Parent SP to execute all the child sp's
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?
If I use a trigger to execute a store procedure? How do I configure the parameters.
Any help will be much appreciated.
Thanks!
dbo.benyos
dbo.benyos
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 1019
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...

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45253 Visits: 39934
I don't have a very good answer for you but I can tell you that using @@IDENTITY, especially if triggers come into play, is a form of "Death by SQL". Use SCOPE_IDENTITY() instead. See Books Online for why.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45253 Visits: 39934
You might also want to lookup what OUTPUT does for you, as well.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 11350
Jeff Moden (9/12/2009)
You might also want to lookup what OUTPUT does for you, as well.

Using the OUTPUT clause of the data modification statement is much to be preferred in many cases.

Both SCOPE_IDENTITY() and @@IDENTITY are unreliable if a parallel plan was used in the statement that affected the IDENTITY column. I am unsure about whether IDENT_CURRENT is likewise unreliable in these circumstances.

All four options (OUTPUT clause, @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT) have their applications, and all are well documented.

When using anything except the OUTPUT clause, I am careful to add an explicit MAXDOP(1).

See this confirmed bug for details. Both 2005 and 2008 are affected, and there are no plans to fix it before at least R2.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search