May 12, 2009 at 9:38 pm
Hi,
I have a four tables Table1,Table2,Table3 and Table4. The Table4 is populated via another system and is a realtime data entry. As soon as the entry is made in table4, I need to perform some calculation and update table3, then i need to perform some calculation and update table2 and the finally table1. This a complete transaction will include from real time data being in entered into table4 and till table one.The commit needs to performed after one complete transaction.
Is trigger is the good idea to perform this task? If yes,then is there any issue with the performance as the number of entries per sec in table4 will be ard 20 or more. If no, is there any alternative methods to achieve this.
this tables are used by frontend application,to show the realtime data.
May 13, 2009 at 6:43 am
Are you considering putting a trigger on each of the tables to get the data to flow through?
Ideally triggers should be short and quick if you use them at all as they do extend the life of the transaction.
Is near-real time acceptable for the business? Do you want the original transaction to fail if one of the later changes fail? If it is you might want to look into service broker or queue the key value(s) for table4 in a staging table and have a job that does the processing outside the original transactoin.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2009 at 10:06 am
Ya Jack, this is the business requirement that the front end must show the real time data.however, we can have a refresh rate of 5min.
Further, its is required the transaction should be complete. any failure should roll back the entire transaction.
Kindly provide more information on the Service Broke as am new to this concept. Will my requirement be met with the implementation of service broker.
Regards,
May 13, 2009 at 1:07 pm
Just to clarify, if the action table1 fails you want the initial action on table4 to fail as well?
I don't know a lot about Service Broker, but I do know it is designed to handle asynchronous operations that in the past you would handle with a queue table and a SQLAgent job.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2009 at 1:19 pm
A trigger could certainly do this, but I've generally found it better to have all the code in the insert proc, instead of having an insert trigger. Not for performance reasons, but because it's going to be a lot easier to maintain and deal with down the road.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2009 at 1:23 pm
If it is possible to do it all in one stored procedure it will be better for BOTH reasons, performance AND maintainability.
* Noel
May 13, 2009 at 9:55 pm
Can i call a stored procedure from within a tigger. The reason being, if some updated happens to the table4, i need to propogate the some status till table1.As you send the SP will be better way of doing this.So can i place a trigger on table4, which will trigger when there is an update to the table4 and the rest will be calculated by the stored procedure.
May 14, 2009 at 6:56 am
naveenreddy.84 (5/13/2009)
Can i call a stored procedure from within a tigger. The reason being, if some updated happens to the table4, i need to propogate the some status till table1.As you send the SP will be better way of doing this.So can i place a trigger on table4, which will trigger when there is an update to the table4 and the rest will be calculated by the stored procedure.
So far as I know, there's no difference between doing that and doing the calculations in the trigger.
What I was suggesting is, you should have a proc that's used for updating table 4, so why not make that proc do all the work? No trigger at all.
If the updates to table 4 are being done through a direct table connection, then you can't do that and you'll need to build a trigger for it. But direct table access is usually (almost always) a bad idea. A proc would be better. If it's not an option for you, then a trigger is needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2009 at 11:20 am
I'll chime in once more to agree with Gus and Noel that, if at all possible, all the code should be encapsulated in a stored procedure. I did not mention is earlier because I assumed that this was a 3rd-party application where you had no control over the original insert.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 17, 2009 at 1:59 am
My business logic shoud get executed whenever, there is an updation to the table4.My business logic cann't be implemented in the stored procedure.My business logic is such that, it should perform some comparison at the bit level(bit by bit comparison) with each of the values in master table(each row), if the bit comparison is success, then i need to take the corresponding values from the master table and populate table3. This i need to perform at each level for table3,table2 and table1......
What if i create a CLR trigger to do this.... Is there any issue doing so!!! if not what needs to taken care for implementing this....
Kindly advice
May 17, 2009 at 10:06 pm
At this point I am going to chime in and suggest that you read the first article I reference below in my signature block and the following blog, The Flip Side.
If you want better answers, the advice in both are quite valuable.
May 17, 2009 at 10:11 pm
[font="Verdana"]One system reaching in and injecting data into the tables of another system is the data equivalent to code in one system reaching into the middle of the code in another system to run something. We call this "intimate coupling" and it's never a good idea.
For some examples as to why it can be bad, what happens to the originating system if it wants to put data in your system and your system is down? Or what happens if you need to change your system?
You would be better off creating an interface of some sort. I would recommend the use of middleware (such as BizTalk) if you have it or a queue of some sort (MSMQ or SQL Server Broker Services) or even an SSIS package. Or you can look at third party data integration tools, but that's a whole step up.
In each case, the interface can copy across the data and then call your necessary logic. So you wouldn't need to rely on triggers.
This is a bad reason to use triggers. You are patching up a mistake. If you can refactor the connection between the systems, please do.
[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply