Yes to me too it is returning only 1 count.
When ist row is being inserted and the trigger fires and there is sucessful insertion and in second insert when trigger fire it dectets the negative values it is erring out.
Correct. And then the third insert attempt runs (since it is in a new batch), but due to the badly coded trigger, it won't catch the violation, so it will insert two more rows. And then the select returns a count of three.
If you got a different result when test-running the code on your test database, you probably either used an old, out-of-support version of SQL Server, or you made a mistake when copying the code.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis