Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yellina
Starting Member
1 Post |
 Posted - 2003-05-21 : 23:53:42
|
| Hi Friends, I may not get the quick answers in any books. Please help me to stay in the right track.I am writing a AFTER trigger (combined trigger for UPDATE, INSERT) in SQL Server 2000, which calls SP1, this SP1 calls SP2 and this Sp2 calls SP3. {This is written by my previous guy and he left the company but I guess it did not implement this trigger}Trigger:- It is on a Table name "Event", which has 10 fields in it. This trigger takes the entered data, it could be single row or multiple rows entered by the user or Stored Proc or an front end application. So, I have to use the cursor to check the values entered, whether they are valid and do some calculations on them. In this while loop of cursor, I am calling first SP (SP1).SP1:- This check whether they are valid, I mean the range etc (which is not constant, its the busic logic, so I cannot use any constraints or rules here). There are 4 fields engfield1(ef1), engfield2(ef2) and cufield1(cf1), cufield2(cf2). But only ef1 and ef2 are entered. {we will have to calcuate cf1, cf2 and based on ef1, ef2 and other 3 fields in another table. This is done by the last SP(sp3)}This SP1 validates and if it is successful it will call SP2.SP2:- This SP2 inserts into the same table all values except cf1 & cf2. So, after inserting in the same table, it call SP3.SP3:- this proc calulates cf1 & cf2 which some formulae in which ef1 & ef2 are used and is set in the same row of the same event table.Now I think there are atleast 2 very bad things going in here, I just realised this evening.1)SP2 tries to insert the values, which is meaningless beacause they are inserted and then only trigger is fired. Ofcourse SP2 is checking whether it has event ID in it, if it has then he is not inserting it. So this if loop doesn't execute because the same eventID is already there. So we are fine here. (Am I?, and I will remove this. Please suggest me here too)2)SP3 updates cf1 & cf2 in the same row of the same table, therefore again the same trigger will fire. (Am I right or wrong. because still trigger is in the process it will not fire a update-trigger which is the same trigger). I guess I am wrong. so my questions area)Does the trigger fire anywhere anytime when we just try to insert, update or delete any row of that table.b)If so, am i going to recursive loop herec)should i go for different trigger for update, insert and delete. What do u sugguest. If it easy to write two triggers for update and insert instead of writing the complex trigger which does the same thing, what should I do. I prefer two trigger because they are less complex , I can copy and paste the same code here except the core part and can do it. but what do u think, does it decreases the performance. Is it very bad practice, writing redundant code.Actually I am using those stored procs sp1, sp2, sp3 because they can be used by the application developers for that specific functionality. So I am trying to resuse the code but If I do it with triggers I am failing because SPs are called one by one, so the triggers can keep on firing. This is the main problem. So when I code for trigger, should I get rid of that modular programming which we write in small junks so that we can use those functions later.When programming triggers, shouldn't I write small junks or call SPs. If not, only thing is copy the same code(logic) in all SPs and paste it in the trigger with some slight modifications. Is this approach very bad in future or This is what how we should think when we write the triggers.Please help. I have to right a big trigger so If i do the basics wrong there is no way I can finish this trigger. This evening I came across above two problems, which are very basic and did not realise for a week. So, I do not want sit for days and debug while my basics about the programming the triggers are wrong. I can also call you, if u can spare few minutes for me. Please be patient and correct me so that I will correct for this group in future..Thank you in advance.Rayan.404-253-1532 (work) I am in Eastern Time Zone-USAyrayan@yahoo.com |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-05-23 : 11:30:51
|
| Regarding your questions:A. If your trigger is for INSERT, UPDATE, it will fire every time you actually insert or update the table. If you have constraints that reject bad data, the bad data will not be inserted/updated, and the trigger won't fire. Otherwise, as long as it is enabled, it will fire.B. Triggers are recursive. If you update the underlying table in its own trigger, you will get into a possibly endless loop. You can always see the @@NestLevel automatic variable to see how many levels deep you are. Your server/database also has an option for how many levels of nesting to allow, which I think maxes at 32. You will get an error if you try an infinite loop, that "the maximum nesting levels has been reached".C. If you have the same logic in both UPDATE and INSERT situations, no use in putting it into two separate triggers.I can understand what sp1 does- you pass it the values of the current row in the cursor and it validates it. You probably have som kind of return value so you can know if its good or not. So far so good. I don't understand what sp2 does- why are you reinserting data? And if, as you say, the data is already there it doesn't reinsert it, what's the point of running sp2? If the trigger has fired, the data will definitely be there.And sp3, why does it update the freshly-inserted or updated data?And if you are trying to calculate values, why not used derived/computed columns for those?Sarah Berger MCSD |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-27 : 13:30:45
|
Okquote:
Hi Friends, I may not get the quick answers in any books. Please help me to stay in the right track.
Take Sarah's advice and re-write it.I don't think you need a trigger at all...And because of all this confusion, why do you think:quote:
{This is written by my previous guy and he left the company but I guess it did not implement this trigger}
Also, I'm sure you can avoid using cursors....Good LuckBrett8-) |
|
|
|
|
|
|
|
|
Trigger concepts