A weird sql server insert problem ?

Question

UPDATE : I have this SSMS tools pack which wraps everything in this -

BEGIN TRAN
ROLLBACK

When I removed above lines and just ran the proc....everything works fine...I guess it was doing an rollback...as SQLMenace said...my bad i guess...never happened before so i was quite confused....anyways...thanks..hopefully it will help someone else....

Hi all,

I have a stored procedure that basically inserts some entries/rows after checking if those entries dont exist before. Now it says that rows affected when I run it but when i open the table it has no new entries....and hence every time i run that proc now it says it has inserted entries when it should actually just check for existing values and not do anything...now it shows something like this -

(1 row(s) affected)

(1 row(s) affected)

when it should be only showing (1 row(s) affected)

Now I am guessing its deleting the row after it inserts it immediately...and that is why it never shows up...now i checked for any update or delete cascade constraints on the table...but I didnt find any...can anyone help me and give some advice on this...

Answer 1

You have a trigger on that table that probably deletes the row

run this to verify, change 'your table name' to the name of the table

select * 
from sys.triggers
where OBJECT_NAME(parent_Id) = 'your table name'

if the trigger doesn't exist post the proc code, it is possible that you are doing a rollback

Answer 2

BTW

you can change the toolpack behavior

SSMS Tools-->New Query Template-->Options--> delete the SQL from the template text

See image below