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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-12-08 : 08:10:57
|
| zveskov writes "Hi,I wrote an insert trigger on Table1 to update a field of Table2. Inside the trigger, a cursor is declared on Inserted table and a while loop is used on the basis @@Fetch_Status value to go through the each inserted record.The trigger works properly for several years. But oneday it was revealed that the field of Table2 was not updated accordingly although related record is inserted into Table1. I looked into the matter and found that inside the trigger, @@Fetch_status retured -1. That is, the cursor is not populated properly.The incident happened almost one year ago. So, I'm very sorry that I cannot remember the SQL Server and OS versions. But ther must be SQL Server 7/2000 and Windows NT4/2000 server.Would anybody pls help me regarding the matter?" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-08 : 08:53:58
|
| Sure!My crystal ball which lets me magically see the code you are referring to shows me that in line 12 of the trigger, you didn't intialize the variable @temp so it's null. That causes a problem in the IF statement in line 23, since it doesn't handle nulls.If that's not it, there must be something wrong with my crystal ball so I apologize.- Jeff |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-08 : 10:47:06
|
jeff please think about my health (i almost fell of my chair there...)  zveskov: we'd really need more info on the problem, but if the field in Table2 was never correctly updated, maybe you didn't select the correct columns from inserted in the cursor... and even better, why on earth are you using cursors in triggers????Go with the flow & have fun! Else fight the flow  |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-08 : 11:33:16
|
| sorry, I can't help myself sometimes ... - Jeff |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-08 : 20:49:28
|
| ha ha ha, you're the man jeff...if your @@Fetch_Status isn't returning anything, then something must be wrong with your source criteria, someone must have changed either the trigger or the table2 schema.anyways, as Spirit said, you can do without the cursor inside the trigger, better create an sp for that, it's easier to maintain and troubleshoot.--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Use of Cursur Inside Trigger