October 20, 2010 at 11:48 am
how can i loop through a result set from a select statement in a trigger?
October 20, 2010 at 12:00 pm
Short Answer: DECLARE CURSOR
Longer Answer: What are you trying to do? Most of the folks around here know many, many ways to avoid actual row loops and still get the job done faster, leaner, and meaner.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 20, 2010 at 12:14 pm
A lot of things I have read indicate that I should be processing all changes as set-based transactions, and that looping is BAD. But, I am trying to understand whether the trigger actually processes as per-row or set-based. If it is per-row, then there is no need to loop through anything. But, if it is set-based, I am wondering how all rows will be processed without some type of looping through the conceptual tables. And, if that is the case, how would I build my trigger to do that looping.
October 20, 2010 at 1:13 pm
jrbass81 (10/20/2010)
A lot of things I have read indicate that I should be processing all changes as set-based transactions, and that looping is BAD. But, I am trying to understand whether the trigger actually processes as per-row or set-based. If it is per-row, then there is no need to loop through anything. But, if it is set-based, I am wondering how all rows will be processed without some type of looping through the conceptual tables. And, if that is the case, how would I build my trigger to do that looping.
Under 98% of circumstances, looping is the evil enemy that all DBA heros are out to conquer and destroy before he turns the common people into sheep... ummm... yeah, that'll work.
Seriously though, yes, under most circumstances, you want to avoid looping unless you've proven it gives you better performance or there is just no other way.
Triggers process in sets. What a trigger does is get involved in the flow of data at a certain point (either before the actual data change, or immediately after) and you have two virtual tables. "deleted" is all of the previous data, either what it looked like before updating or what you're deleting from the table. "inserted" is the new data. What it looks like after the update, or what you're actually inserting.
Working off these tables, you work set based.
Under the hood, yes, SQL does loop. Look into 'serial update' or 'quirky update' to find proof of this. It does this at the machine level and there's books out ther that will describe it in more detail and with better, clearer examples then I can hope to in a forum. You cannot directly access this looping... and you really don't want to.
So, this brings me back to the question earlier. What are you trying to do with your trigger loop, and perhaps we can help you find a better approach.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 20, 2010 at 1:25 pm
Oracle fires triggers on a per-result basis. MS SQL fires the trigger once, and you have full access to all modified rows by using the "inserted" and/or "deleted" virtual tables.
The vast majority of the time, you don't have to build the looping. The SQL engine will do that for you. You just declare what you want done with the data, and let it figure out how. That's what makes SQL a "declarative language".
Here's an example of a set-based trigger:
create trigger MyTrigger on MyTable
after update
as
insert into MyLogTable (Col1, Col2)
select deleted.Col1, deleted.Col2
from deleted
left outer join inserted
on deleted.ID = inserted.ID
and (deleted.Col1 != inserted.Col1
or deleted.Col2 != inserted.Col2);
That trigger, given real tables, columns, etc., will use the virtual "deleted" table (which has the pre-update data in it), to insert the old values into a log table. First it compares the values in "deleted" to the values in "inserted", and makes sure at least one is different. This prevents useless logging of rows where no value was changed.
And it will work no matter how many rows are updated in a command. No need for explicit looping.
(The trigger would actually need to have some further complexities added to it to be a fully functional piece of code. This is just a sample. For one thing, if a row is changed from a value to Null, the trigger may not behave properly, depending on the settings of the connection and server.)
- 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply