anthony.green (12/19/2012)
@@RowCount only looks at the last statement which in this case would be the DECLARE which will always return 0 rowsUse COUNT(*) from the INSERTED table instead.
But if the trigger is after an insert or update, then you will always have something in the inserted / deleted tables so the need to check wouldn't matter and you wouldnt need the rollback, as something as been inserted or updated.
Actually the declare statement doesn't effect the value of @@rowcount. See the code bellow that shows that.
select * from sys.objects
declare @i int
select @@rowcount
As for the original question – pleas specify what is not working? Do you get an error message? Does it seems to work but nothing gets modifies? Do you see modifications in the data but not the one that you expected? Also take into consideration that you wrote a trigger that most chances won't work correct in case that more then 1 record will be modified (I say most chances because you didn't specify what you are trying to do and what actually happens)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/