Link to home
Start Free TrialLog in
Avatar of goldylamont
goldylamont

asked on

@@ROWCOUNT and delete trigger question

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of goldylamont
goldylamont

ASKER

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
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>> 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.
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!