How to define referential-integrity in this scenario for 2 SQL Server tables?

Question

I have 2 tables in SQL Server 2005 as follows

Table A

• ActionID (PK,int, not null)
• ProgressID (uniqueidentifier,not null)
• ReferID (uniqueidentifier, not null)
• Field XYZ (varchar(50), not null)
• Field MNO (tinyint, not null)

Table B

• TrackID (PK,int, not null)
• ProgressID (uniqueidentifier,not null)
• ReferID (uniqueidentifier, not null)
• Field ABC (varchar(20), not null)
• Field EFG (datetime, not null)

Now I have a specific question:

The ProgressID in both the tables refer to the same entity. And I want to establish an integral relationship so that Deletion of ProgressID in Table A is not possible when value exisits in Table B. How to do that?

Answer 1

I would recommend a before delete trigger for this. Something like

create trigger tr_tableA_progressId
on TableA for Delete
as 
    if exists
        (select 'true'
        from dbo.TableB
        where TableB.progressID = (select progressID
                                  from deleted d))

        BEGIN
            RAISERROR 'Cannot delete progressId exists in TableB'
            ROLLBACK TRAN
        End

I do not know of a constraint that will enforce what you would like.