TR_TAXID_DELETE

CREATE trigger TR_TAXID_DELETE on dbo.TAXID for delete
as
begin
    set nocount on;

    -- raise error if this is the only tax ID for a state that is used in any 1099 distribution

    if exists(
        select * 
        from DELETED D
        left outer join dbo.TAXID T on D.STATEID = T.STATEID and T.ENTITYTYPECODE = 1
        left outer join dbo.FINANCIALTRANSACTION1099DISTRIBUTION FTD on FTD.STATEID = D.STATEID
        left outer join dbo.FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION FTAD on FTAD.STATEID = D.STATEID
        left outer join dbo.VENDOR1099DISTRIBUTION VD on VD.STATEID = D.STATEID
        where (D.ENTITYTYPECODE = 1) and (T.ID is null) and (FTD.ID is not null or FTAD.ID is not null or VD.ID is not null))
        begin
            raiserror('ERR_TAXID_STATE_BEING_USED', 16, 1);
            rollback;
        end
end