Solved

@@ROWCOUNT and delete trigger question

Posted on 2006-06-12
8
699 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 143

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 143

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 143

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

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

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

791 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