Ken,

SQL Server's DRI is implemented as a constraint. Constraints are tested (or fired) before the operations are actually made. A trigger on the other hand fires after operations occur. The DRI will always cause the DELETE statement to be aborted. If you need to have a cascading Delete, you'll have to implement all the DRI using triggers.