Trigger: Looping though multiple rows in the deleted table?

Question

Trigger: Looping though multiple rows in the deleted table?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

... and, yes... that "feeling" goes towards delete triggers, 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lloyd Harrison SSC Veteran Points: 237 More actions · Answer 2

Hi Jeff,

Thanks for all your help with this. I feel the same way you do with the CASCASE. I think it is the wrong way to go. But I'am just a messanger.

Anyway, I think I found the issue:

Table ZIP

PK_ID

ZIP

STATE

Table 2 DISTRICT

PK_ID

DISTRICT_NAME

FK_ZIP

Table 3 SCHOOL

PK_ID

SCHOOL_NAME

FK_DISTRICT

FK_ZIP

I see the problem here, but on the other hand, I don;t see a problem here.

If I delete the ZIP, I want to delete the SCHOOL and DISTRICT

If I delete the DISTRICT I want to delete the SCHOOL

Makes sense, right?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Yes, it makes sense, but SQL sees it as a possible infinite loop. It isn't, but the code has to err on the side of false positives in this case.

Either manage it with a trigger, or, better yet, in the proc that deletes districts. (Keeping it in the proc helps document it and makes it easier to refactor/maintain.)

I agree with Jeff that deletion usually needs to be in the code. I've had a few instances where I couldn't, and had to do it with triggers, but in those cases I make sure it's documented and that the trigger raises severity 10 errors, so testing the proc makes those messages visible.

- 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

darpanshah23 Valued Member Points: 67 More actions · Answer 4

Thanks for better solution with minimum lines of code.