| Author |
Topic |
|
Stanley Tan
Starting Member
25 Posts |
 Posted - 2002-06-07 : 20:31:15
|
| How do I make SQL Server delete triggers execute on a per row basis. In Oracle, you use FOR EACH ROW --> how do you do this in SQL Server?For example, I have a delete statement that deletes multiple rows. The trigger should fire for each row deleted. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-07 : 21:22:44
|
| Oracle does not use a set-based approach in its triggers. In SQL Server, a trigger does not fire for each row deleted, it fires once per DELETE statement, and ALL rows that are affected by the DELETE are handled in one operation.What specifically do you want to do on a row-by-row basis? Realize that in a relational database system, row operations are vastly more inefficient that treating the entire set as one object and processing it accordingly. This is NOT a shortcoming in SQL Server, although your previous experience with Oracle might make you think otherwise. In truth, Oracle is doing it the "wrong" way by doing row-by-row operations.Edited by - robvolk on 06/07/2002 21:50:09 |
 |
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-06-08 : 06:13:32
|
| Hmm... that makes a lot of sense. I guess I just need the number of rows deleted. @@ROWCOUNT has this, right? Is there anything I should watch out for when using @@ROWCOUNT?Off topic, but what is the difference between @VARIABLE and @@VARIABLE? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-08 : 08:02:46
|
| Yes, @@ROWCOUNT returns the number of rows affected by the LAST statement executed. If you need to get the rows deleted, check @@ROWCOUNT immediately after the DELETE statement, before you run another statement.A @@ variable is a system variable, like @@ROWCOUNT, @@IDLE, @@SPID; you could think of them as functions, but technically they're not. A @ variable is defined by the user and can be named anything and can store anything. If you wanted to store the number of rows deleted, you can do this:DECLARE @rowsDeleted intDELETE FROM myTableSET @rowsDeleted=@@ROWCOUNTYou don't need to do this in a trigger, just include it in the batch that deletes rows. |
|
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-06-08 : 08:13:26
|
| @@name is a global system maintained variable@name is a user defined variableed:Where is that refresh function when you need it.Edited by - LarsG on 06/08/2002 08:14:26 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-08 : 08:31:52
|
Dunno, mine's also broken  |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-06-10 : 22:49:11
|
Hmmm... Is this why my Delete triggers aren't working the way I thought?  If the trigger fires only once per statement, does it sort of call itself recursively for all rows? Else, what would happen if you try to SELECT s/t FROM Deleted? Does the Deleted table hold more than one row?Sarah Berger MCSD |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-06-10 : 22:57:57
|
| Sarah,There is NO recursion...It treats SETS not ROWS. A SET can contain 0, 1 or a billion rows...The DELETED and INSERTED tables hold ALL rows affected...DavidM"SQL-3 is an abomination.." |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 07:02:20
|
| David is right, if you are writing your trigger code as if it will only handle single rows, it will not behave as you expect. If you write triggers to handle data as if it were a regular table in a SELECT statement, then they'll work correctly. |
|
|
|
|
Delete Triggers