Solved

@@ROWCOUNT and delete trigger question

Posted on 2006-06-12
8
695 Views
Last Modified: 2012-08-13
Hi all, I'm trying to figure out a trigger and why it isn't working properly for a database project that I just recently took over. I want to change the trigger to work and have some ideas how, but I want to make sure I fully understand why the previous programmer set up the trigger in this way.

1) Ok, first I need to understand--what is the difference between these three variables: ROWCOUNT, @ROWCOUNT, @@ROWCOUNT? I understand that rowcount is that number of rows being affected in an update or deletion but I'm confused as to what adding the @ sign, or two @@ signs means.

2) Here's abbreviated code for the trigger. I'll leave my questions as comments within the code. Basically I need help understanding how/when/why the @@ROWCOUNT variable changes to hopefully understand why this code is set up this way:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER  TRIGGER [dbo].[trgTransfereeTourRealtor_IDRealtor]
ON [dbo].[TransfereeTourRealtor]
FOR UPDATE,INSERT,DELETE
AS
DECLARE @SOMEVARIABLE INT
DECLARE....
DECLARE....


--*****AT THIS POINT, @@ROWCOUNT=1

IF @@ROWCOUNT = 1
BEGIN

--*****AT THIS POINT, @@ROWCOUNT=0,  WHY IS THIS? IT DOESN'T SEEM LIKE ANYTHING HAS HAPPENED YET

      SELECT @ID_TransfereeTourRealtor = ISNULL(ID_TransfereeTourRealtor,-1) FROM INSERTED
      SELECT @ID_Transferee = ID_Transferee FROM INSERTED
      SELECT @ID_Realtor = ID_Realtor FROM INSERTED
      SELECT @ID_RealtorDELETED = ID_Realtor FROM DELETED
      SELECT @ID_TransfereeTourRealtorDELETED = ID_TransfereeTourRealtor FROM DELETED

--******THIS NEXT IF STATEMENT IS HERE I BELIEVE TO DETERMINE WHETHER THE RECORD IS BEING DELETED OR UPDATED
      IF ISNULL(@ID_TransfereeTourRealtorDELETED,0)<>0 AND ISNULL(@ID_TransfereeTourRealtor,0)=0
      BEGIN
               --get key for subsequent delete
            SELECT @ID_TransfereeRealtor = ID_TransfereeRealtor
            FROM dbo.TransfereeRealtor
            WHERE ID_TransfereeTourRealtor = @ID_TransfereeTourRealtorDELETED
               
                --set to zero to avoid the delete trigger on dbo.TransfereeRealtor
            UPDATE dbo.TransfereeRealtor  
            SET ID_TransfereeTourRealtor = 0
            WHERE ID_TransfereeTourRealtor = @ID_TransfereeTourRealtorDELETED
            
                SET @RowCountNum = @@ROWCOUNT

--*****here is where the code "breaks". Since ROWCOUNT=0 at this point, the IF statement is always true
--*****and it posts the error message.
            
                IF @RowCountNum <> 1
            BEGIN
                  SET @ErrorMessage = "SOME ERROR MESSAGE"
                  ROLLBACK TRAN
                  RAISERROR (@ErrorMessage, 16,1)
            END
            ELSE
            BEGIN
                  DELETE FROM dbo.TransfereeRealtor
                  WHERE ID_TransfereeRealtor = @ID_TransfereeRealtor
            END
      END
      ELSE IF UPDATE(ID_Realtor)
      BEGIN
        --****THIS CODE WILL UPDATE THE REALTOR TABLE
      END
END

thanks!
goldy
0
Comment
Question by:goldylamont
8 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 350 total points
ID: 16888591
>IF @@ROWCOUNT = 1
>BEGIN
>
>--*****AT THIS POINT, @@ROWCOUNT=0,  WHY IS THIS? IT DOESN'T SEEM LIKE ANYTHING HAS HAPPENED YET

The value of @@ROWCOUNT (and @@ERROR) is reset IMMEDIATELY after used, ie it is reset for each sql statement.
hence, if you want to keep the value, you have to store it into a variable before using the value elsewhere.

>--******THIS NEXT IF STATEMENT IS HERE I BELIEVE TO DETERMINE WHETHER THE RECORD IS BEING DELETED OR UPDATED
>     IF ISNULL(@ID_TransfereeTourRealtorDELETED,0)<>0 AND ISNULL(@ID_TransfereeTourRealtor,0)=0
yes.
if the @ID_T...Delete is null, it means that no row was in deleted, hence no delete.
if the coalesce(@ID..., 0) value is equal to 0, would mean no delete, hence a value <> 0 means it was a delete (or update)

if the @ID_T is null, it means it was a delete. hence, coalesce(@id, 0) would be 0


> SET @RowCountNum = @@ROWCOUNT
this will return the value of @@ROWCOUNT in regards to the statement before, which is the UPDATE of table TransfereeRealtor, and NOT the initial delete !!!


hope this helps
0
 
LVL 6

Assisted Solution

by:davbouchard
davbouchard earned 100 total points
ID: 16888594
@@ are global functions/variables supplied by the system.

As for the rowcount, after a update stmt or otherwise it will set the global variable @@rowcount to the number of rows affected.

@ is a local variable.  Which the programmer/user declares.  It is only available in the script/sp they create and will only change when they decided.   eg..    Declare @rowcount int

For each update stmt the @@rowcount will change.

However, if the user sets the @rowcount to 5, it will stay 5 until the user changes it to another number.
0
 
LVL 20

Assisted Solution

by:Sirees
Sirees earned 50 total points
ID: 16888620
<<) Ok, first I need to understand--what is the difference between these three variables: ROWCOUNT, @ROWCOUNT, @@ROWCOUNT? I understand that rowcount is that number of rows being affected in an update or deletion but I'm confused as to what adding the @ sign, or two @@ signs means. <<

RowCount - Causes SQL Server to stop processing the query after the specified number of rows are returned.

@RowCount - local varaible

@@RowCount - Returns the number of rows affected by the last statement.




0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:goldylamont
ID: 16888630
ok, then, what about ROWCOUNT without any @ sign before it? Is the variable ROWCOUNT the same as a variable @ROWCOUNT? meaning that they have local script-wide scope and is user defined?

Also, why does the @@ROWCOUNT go from 1 to 0 after the IF/BEGIN statements above without any other code running?

thanks
0
 

Author Comment

by:goldylamont
ID: 16888667
please disregard my comment above as i didn't see the first posting above that deals with this issue. Whew! you guys are answering faster than by browser will reload.

give me a second to let this sink all in and i'll divvy up the points or ask anything else if i need clarifications.

thanks.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 350 total points
ID: 16888724
the ROWCOUNT got explained meanwhile.

assume you have a table with 100 rows.

SET ROWCOUNT 1
SELECT * FROM yourtable

will return only 1 row !

to reset that setting, to "return all rows":
SET ROWCOUNT 0

>Also, why does the @@ROWCOUNT go from 1 to 0 after the IF/BEGIN
it is reset by the IF statement itself, this is "by design"

0
 

Author Comment

by:goldylamont
ID: 16888796
>>> SET @RowCountNum = @@ROWCOUNT
>>this will return the value of @@ROWCOUNT in regards to the statement before, which is the UPDATE of table TransfereeRealtor, and NOT the initial delete !!!

ok, so in essence, anytime @@ROWCOUNT is even mentioned in the code, it will return whatever value it has and then reset itself to 0. This means even if it's in an IF statement or variable assignment; once it's used once, it's reset to 0, correct?

also, @@ROWCOUNT is reset after any type of SQL statement, so that it is reflective only of the most recent SELECT, UPDATE or DELETE statement, correct?

thanks guys.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16888810
ok, so in essence, anytime @@ROWCOUNT is even mentioned in the code, it will return whatever value it has and then reset itself to 0. This means even if it's in an IF statement or variable assignment; once it's used once, it's reset to 0, correct?

absolutely!


also, @@ROWCOUNT is reset after any type of SQL statement, so that it is reflective only of the most recent SELECT, UPDATE or DELETE statement, correct?

again, correct!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert null in sql server 12 34
Need help constructing a conditional update query 16 47
replace \ by - in select 4 21
abs operation in sql server 15 15
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

809 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