0

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 Order and 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 Order or Warehouse with 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

Diagram

Trigger code:

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
  • Totally unrelated with the question but, are you sure about wanting this i created a trigger to delete records from Order and Warehouse when a specific user is deleted from User table. This sounds very dangerous for the business to me – Laiv Jun 6 at 7:47
  • @Laiv i do not see other way, because if you look on Order table i have two fields pointing to user's id (same also for Warehouse). I tried to create such relationsions with two foreign keys from order to user btu it's not permitted from ssms. Then i read that in this case i would need to create trigger which i did. What would you do? – Eldorado Jun 6 at 7:56
  • What ActionUserID and ActionReceiverID are? – Laiv Jun 6 at 8:09
  • @Laiv from business perspective it's like there is an order. ActionUserID is the user whom created an order and ActionReceiverID is the user who will handle the order. – Eldorado Jun 6 at 8:13
  • 1
    @Fabio i think i put something like isEnabled this will avoid to delete Order – Eldorado Jun 6 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.