Solved

@@ROWCOUNT and delete trigger question

Posted on 2006-06-12
8
717 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 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