Solved

@@ROWCOUNT and delete trigger question

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

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

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

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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 …
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

19 Experts available now in Live!

Get 1:1 Help Now