0
I am mounting a query to be executed via DAPPER/C#, my idea is not to check if the data exists in the table returning the object to the application, but rather, to check directly in the data bank of a single time.
I know it’s possible to use the MARGE, but with it I have the complication of not being able to use the WITH CHANGE_TRACKING_CONTEXT.
That’s why I built my query using ROWCOUNT that returns the amount of line affected and if it is equal to zero do the Insert.
My question is whether it is reliable to use the ROWCOUNT for a situation like this, being that my table may have Rigger and making other business rules including update and Insert in other tables.
WITH CHANGE_TRACKING_CONTEXT( 0x4143544956452D53..)
UPDATE MINHA_TABELA
SET CAMPO2 = 44
,CAMPO1 = 1
WHERE ID = 1
IF @@ROWCOUNT = 0
BEGIN
WITH CHANGE_TRACKING_CONTEXT( 0x4143544956452D53..)
INSERT INTO MINHA_TABELA (ID, CAMPO1, CAMPO2)
VALUES (1, 1, 44)
END
Is yes...........
– novic
@Virgilionovic, even if the table has Rigger, knows whether it returns the affected rows of a Rigger or only the same table?
– Marco Souza
From what I understand you run via Dapper if yes gives now Rigger have no way to check and this is not in your question
– novic
Yes, I ended up not mentioning the part of Rigger even because I think it was a little obvious that a table has it. but I will edit the question.
– Marco Souza
according to the official documentation https://docs.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-ver15 "Returns the number of Rows affected by the last statement" i.e., from the last command, then an update and then pick up the value of rowcount will return the changed records in that command, now if a Trigger makes changes, it is already another command
– Ricardo Pontual
It is not at all obvious that any table has triggers.
– bfavaretto
@bfavaretto for me is, but I changed the question.
– Marco Souza