I have such situation that there are two foreign keys to
Users table from
Order table and two foreign keys from
Warehouse table to
Users table. As we know sql server do not permit more than one relationship between tables to one field as i have now to
Users ID especially with
cascades therefore i created
trigger to delete records from
Warehouse when specific user is deleted from User table.
My concern is now there is no check integrity. For instance if from external application somehow someone put some of user to
ID of user which not existing there will be no error and not existing id will be inserted. Normally when i would just have foreign keys instead of
trigger database would drop such action. is there any way to achieve that or maybe trigger solution is wrong?
Here below find my diagram from ssms and trigger setup
ALTER trigger [dbo].[tr_T_User_Delete] on [dbo].[T_Users] instead of delete as begin if (@@rowcount = 0) return delete o from dbo.T_Order o inner join deleted d on d.ID = o.FK_ActionUserID delete o from dbo.T_Order o inner join deleted d on d.ID = o.FK_ActionReceiverID delete o from dbo.T_Warehouse o inner join deleted d on d.ID = o.FK_ActionUserID delete o from dbo.T_Warehouse o inner join deleted d on d.ID = o.FK_ActionReceiverID delete u from dbo.T_Users u inner join deleted d on d.ID = u.ID end