Problem with delete trigger

I am trying to write a delete trigger that fires when the user deletes the only record for a given entity in the table. In this situation I have an entity PMA which can have one or many uses. I want the trigger to fire if the user tries to delete the last use record for that PMA_NUM from the PMA_USE table.

Here is my code:

if (SELECT Count(pma_use.pma_num) FROM dbo.PMA_USE
inner join dbo.deleted as D1 on dbo.PMA_USE.PMA_NUM= d1.PMA_NUM) = 0
BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

I seem to be getting inconsistent results when I create the trigger and when I try to test it re the deleted table

Sometimes when I run the trigger script in Management Studio Express, it has trouble with my using dbo.deleted and sometimes it doesn't

When I try to test the trigger by using MSE to view the table and delete the last use record for a PMA, I get an invalid object cannot find 'dbo.deleted' or cannot find 'deleted'

What am I doing wrong?

Roberta

1 - Use just [deleted]

2 - you do not need to count in order to prove existence. Use EXISTS operator.

if exists(select * from dbo.PMA_USE as a innner join deleted as b on a.PMA_NUM = b.PMA_NUM)

BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

AMB