njtynhj

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 procedure/trigger will affect all columns in a row.

Any guidance here is greatly appreciated.

Edited with the below (work in progress).

I've successfully created my stored procedure but when I execute nothing happens. The screen just says 'executing' and it stays like that until I stop it. Any ideas?

Msg 266, Level 16, State 2, Procedure DeleteCustomer, Line 2
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Well, you could use a trigger on your Customers table to update the related tables whenever Customers.is_deleted is changed. SQL Server provides inserted and deleted virtual tables in triggers, so you can compare to see whether is_deleted has changed, and update the other tables as necessary.

However, I'm wondering if you actually need additional is_deleted fields for your "child" tables. If, for example, CustomerContactDetails.is_deleted is always going to match Customers.is_deleted, you could just create a view that has all the contact details, and pull the is_deleted value from the base Customers table.

If you do go the route of creating a trigger, I would just recommend reading over the documentation.

This would likely work, you would of course need to provide inputs for the rest of the tables with their proper relationship keys.

This is a decent explination of Inserted Logical Tables if interested.

Something along these lines should be fairly close. I would caution you though, your table structures are problematic in a number of ways. One of the biggest issues to me is that you have Cust_ID as a varchar(12) but you don't have a column for customer name. Then you have referenced this Cust_ID as foreign key in other tables. I am assuming this column is really the customer name. And the issue is that you have designed your tables in such a way that you can't change a customer name once it is created. That is generally not a good approach as people and business names can change. At any rate that is a topic for another day.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.