Rowset-Based Logic?

Posted on 2009-04-26
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.

Question by:expertsoul
    LVL 142

    Expert Comment

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

    Open in new window

    LVL 12

    Author Comment

    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.
    LVL 142

    Accepted Solution

    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.

    LVL 12

    Author Closing Comment

    Thanks a lot. That makes it clear.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now