[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Rowset-Based Logic?

Posted on 2009-04-26
4
Medium Priority
?
1,060 Views
Last Modified: 2012-05-06
Hi Experts,

I was reading about DML triggers. One of the common issue we face while dealing with triggers is modification for multiple rows. As triggers are set based, we need to handle all the rows at once.

One of the solution is to use Cursors, which is not recommended due to performance constraints.

Another solution, I found in "Unleashed (SQL Server 2005)" is "Rowset-based logic". It doesn't say more about it. I was wondering if anyone have used or knows how this solution works.

Also, if there is any other solution you know, please let me know.

Thanks.
0
Comment
Question by:expertsoul
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24237426
you have to rely on INSERTED (and DELETED) virtual tables directly, for example:
CREATE TRIGGER trg_name
  ON yourtable
 FOR UPDATE
AS
BEGIN
  UPDATE t 
     SET somefield = x
   FROM othertable t
   JOIN INSERTED i
     ON i.primary_key = t.foreign_key
END

Open in new window

0
 
LVL 12

Author Comment

by:expertsoul
ID: 24237573
Thanks Angel.

That's another way of doing it. But not always processing will be this simple. We may have some if condition or processing based on existing or new data, which may engender a need for cursor.

I am not clear about what is meant by "Rowset-based" logic and how it can help me implementing such triggers.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24237603
above code IS rowset-based logic.

depending on what the "if" is you are speaking of, you will need one or more of the following:
* CASE expression
* additional conditions (WHERE   AND/OR/... )  on the query
* multiple queries handling the data (still rowset based)

only if really, really, you need to process each row data individually, then you can handle the data using a non-cursor, but still row-by-row process using a table variable holding the data to process (with some primary key), and a loop that takes 1 row each time and deletes that row from the table variable, until that table is empty. will be faster than a cursor, but still allow row-by-row handling.

0
 
LVL 12

Author Closing Comment

by:expertsoul
ID: 31574735
Thanks a lot. That makes it clear.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question