new to triggers

[barbola]

I have a table where records get imported and I have a few more fields that need to be calculated. Some fields are dependant on the resulting calculation of other fields.

I started with Stored Procs but the procedure would run against the whole table. So as someone suggested, I created triggers on the table (Update and Insert).

To me this means the triggers will fire when a record is updated or inserted, and only that record will be affected, right?

Okay, so I have 4 triggers. I get the following error when I add a record to the table:

[blue]ODBC--insert on a linked table 'dbo_MyTable' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).(#217)[/blue]

I've looked up the error but I don't understand what I am reading.

 

[timfoster]

post up the code for your trigger, so we can have a look

 

[ousoonerjoe]

view nesting level exceeded (limit 32).

Last time I saw that, I had created a scenario where the triggers were cause each other to fire repeatedly. I found that consolidating them into a single trigger and being mindful of ordering resolved this issue.

Still, post your triggers so we can see what you've got.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[SQLSister]

And since you are new to triggers, you need to know that the trigger must be written to handle multiple record inserts/updates/deletes. It will fire only once per batch, not once per record.

"NOTHING is more important in a database than integrity." ESquared

 

[barbola]

OMG So I was trying to modify my code to hide some details and give my tables and fields generic names, and realized a huge issue as to why my trigger wouldn't work. So what I am doing instead is posting my Stored Procedures that I started with.

If there is a way to have these calculations done each time a record is inserted. I don't know what you mean by "batch". Does that mean they don't fire until all the records in the file that goes through the Python script are inserted?

This one looks up a WeightRange based on the Weight:

CREATE PROCEDURE .[SP_WeightRange] AS 
UPDATE Table1
set Table1.WeightRange= WeightRanges.WeightRange from Table1
 	join WeightRanges on (Weight >= LowerWt and Weight < UpperWt)
	where Table1.WeightRange is null
GO

This one calculate Thing1 based on Weight and Height

CREATE PROCEDURE .[SP_CalcThing1] AS 
UPDATE Table1  
	set Thing1 = 
	(65-(0.50 * Weight) + (0.055 * Height))
	where Thing1 is null
GO

This one looks up the value of Thing2 in another table based on Weight

CREATE PROCEDURE .[SP_Thing2] AS 
UPDATE Table1
set Table1.Thing2= Table2.Thing2 from Table1
 	join Table2 on (Weight >= LowerWt and Weight < UpperWt)
	where Table1.Thing2 is null

GO

This one looks up Thing3 in another table based on Height

CREATE PROCEDURE .[SP_Thing3] AS 
UPDATE Table1
set Thing3= Thing3 from Table1
 	join Table3 on (Height >= LowerHt and Height < UpperHt)
	where Thing3 is null

GO

This one looks up Thing4 in another table based on the value of Thing1 (see SP_CalcThing1)

CREATE PROCEDURE .[SP_Thing4] AS 
UPDATE Table1
set Table1.Thing4 = Table4.Thing4 from Table1
 	join Table4 on (Thing1 >= LowerThing and Thing1 < UpperThing)
	and Table1.Thing4 is null

GO

This one looks up Thing5 in another table based on the value of Thing4 (see SP_Thing4) and the Weight

CREATE PROCEDURE .[SP_Thing5] AS 
UPDATE Table1
set Table1.Thing5 = Table5.Thing5 from Table1
 	join Table5 on (Table1.Thing5 = Table5.Thing5)
	and (Weight >= LowerWt and Weight < UpperWt)
	where Table1.Thing5 is null

GO

So the last two are dependant on others. I can't get it all into one Trigger. I fail miserably. Help is much appreciated.

 

[ousoonerjoe]

Put all of it into one trigger. Each Update statement will fire the series of triggers which is causing a ripple effect that will never end (your error). Placing them all in one procedure will avoid this.

Also, doing a join with the INSERTED table will do a mass update across all records inserted/updated instead of just one. (SqlSister can explain this much better than I can.).

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[barbola]

I didn't know I could put more than one SQl statement in the trigger. Some examples I find are really long and I have a hard time following.

SO what would be the proper syntax? Just one after another? Do I need to say GO between each one? Give them trigger naems? Or just one big trigger with each staement one after the other and a big GO at the end?

It's not a table that is inserted really. It's a text file with a bunch of records inserted one at a time I think.

 

[SQLSister]

Never use Go when doing multiple statments in a trigger. Go ends the batch and no statmenets after the go will be executed. Just write the statements in the order you need. They will all be part of the same transaction and if one fails all will fail (including the insert that called them.

What I mean by batches is that if someone insert 10 records using one SQL statement, the trigger will fire once, not ten times. You have to account for this behavior when designing triggers as you do not know when someone will do this.

You do understand a trigger will fire whenever anyone inserts data into the table, not just for your import? If you want it just for your import, then import to a work table and then use your procs to udate the info in the work table, then insert to the production table.





"NOTHING is more important in a database than integrity." ESquared

 

[barbola]

Yes, that all makes sense. We hired a new IT professional who knows all this stuff, finally!!!! I have some help