• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1109
  • Last Modified:

Rowset-Based Logic?

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
expertsoul
Asked:
expertsoul
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
expertsoulAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
expertsoulAuthor Commented:
Thanks a lot. That makes it clear.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now