SQL Server - update field in one table and updated related fields in multiple other tables, trigger, stored procedure or combination of both?
I'm working on a college problem and need to create a stored procedure to soft-delete a record in one table and the associated records in other tables. I'm trying to figure out how I might approach this and hence here I am. I have the following tables: As you can see, all of these tables have an IsDeleted column set with the data type bit and defaults to 0 (to allow for soft deletes). What I'm trying to achieve is that when dbo.Customers(IsDeleted) is updated to bit 1, the following records automatically update to bit 1 also: My initial thought is to set up a stored procedure to allow the end-user to easily update the dbo.Customers(IsDeleted) column and then a trigger to update the related records but I'm not clear if this is the best approach and if so where to start. My research into solving the problem so far has further confused me as it seems I need to be very careful that my code restricts the update of the IsDeleted columns ONLY as a standard stored procedu...