Hi,
Users perform updates on the table and they can affect more than one row in
single DML statement. What if I want to perform something on each record in
Inserted table? Should I necessarily declare a cursor?
Thanks in advance,
Leila

It depends on what you need to do.  If you need to execute a stored proc for
each individual row, then a cursor *may* be appropriate.  However, most
times, you can perform an update at set-level, based on the inserted virtual
table.

How about giving us the specific requirements?

Users perform updates on the table and they can affect more than one row in
single DML statement. What if I want to perform something on each record in
Inserted table? Should I necessarily declare a cursor?
Thanks in advance,
Leila

Hello Leila,

Can you give us an example of what you want to do?

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

I appreciate for all of responses!
Kent,
Imagine that I want to send two columns of Inserted table (for each row)
into a stored procedure. This is the scenario...



Show quoteHide quote

Leila (Lei***@hotpop.com) writes:
You would indeed have to run a cursor of "inserted" to do that. But
whether you should actually do it is another matter. If the procedure
is complex enough, and it's reasonable to assume that not very many
rows gets inserted at a time, then it may be OK. But if the procedure
is performing some trivial operation, it's better to incorporate the
logic into the trigger. Essentially, this is a trade-off. (And if you
choose the easy way out and run the cursor, be prepared that you will
have to rework that in the future. Been there, done that.)

Using a CURSON inside a TRIGGER is definitely the wrong thing to consider!!!
(Ooooh,The horror of it all...)

Provide more information about what you are attempting to accomplish and
perhaps someone here can help you do so in a manner that won't create such
extreme performance penalties on the server.

It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 )



The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.


Show quoteHide quote

Leila wrote:
The simple answer is no. Don't use cursors in triggers.

Don't ever assume that only 1 row will be affected in a trigger.
Instead make sure your trigger can cope equally well with either 0, 1
or N rows affected. The details depend on exactly what you want to
achieve. The key is that you make use of the DELETED and INSERTED
virtual tables. DELETED and INSERTED show the "before" and "after"
state of the rows affected by any update operation.

Here's a very simple example:

CREATE TRIGGER trg ON tbl FOR INSERT AS

INSERT INTO some_other_table (col1, col2)
SELECT col1, col2
  FROM inserted ;

GO

Hope this helps.

Marking (Not just Finding) Duplicate Values ......
Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string