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].[trgTransfereeTourRe altor_IDRe altor]
ON [dbo].[TransfereeTourRealt or]
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_TransfereeTourRe altor,-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_TransfereeTourRealtorD ELETED = 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_TransfereeTourR ealtorDELE TED,0)<>0 AND ISNULL(@ID_TransfereeTourR ealtor,0)= 0
BEGIN
--get key for subsequent delete
SELECT @ID_TransfereeRealtor = ID_TransfereeRealtor
FROM dbo.TransfereeRealtor
WHERE ID_TransfereeTourRealtor = @ID_TransfereeTourRealtorD ELETED
--set to zero to avoid the delete trigger on dbo.TransfereeRealtor
UPDATE dbo.TransfereeRealtor
SET ID_TransfereeTourRealtor = 0
WHERE ID_TransfereeTourRealtor = @ID_TransfereeTourRealtorD ELETED
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
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].[trgTransfereeTourRe
ON [dbo].[TransfereeTourRealt
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_TransfereeTourRe
SELECT @ID_Transferee = ID_Transferee FROM INSERTED
SELECT @ID_Realtor = ID_Realtor FROM INSERTED
SELECT @ID_RealtorDELETED = ID_Realtor FROM DELETED
SELECT @ID_TransfereeTourRealtorD
--******THIS NEXT IF STATEMENT IS HERE I BELIEVE TO DETERMINE WHETHER THE RECORD IS BEING DELETED OR UPDATED
IF ISNULL(@ID_TransfereeTourR
BEGIN
--get key for subsequent delete
SELECT @ID_TransfereeRealtor = ID_TransfereeRealtor
FROM dbo.TransfereeRealtor
WHERE ID_TransfereeTourRealtor = @ID_TransfereeTourRealtorD
--set to zero to avoid the delete trigger on dbo.TransfereeRealtor
UPDATE dbo.TransfereeRealtor
SET ID_TransfereeTourRealtor = 0
WHERE ID_TransfereeTourRealtor = @ID_TransfereeTourRealtorD
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>> 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.
>>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!
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!
ASKER
Also, why does the @@ROWCOUNT go from 1 to 0 after the IF/BEGIN statements above without any other code running?
thanks