Avoiding data integrity issues

[mario-campos]

It's important that the information in your databases remains accurate, consistent, and complete. Poorly designed queries can result in missing or incorrect data.

Example scenario

The following SQL transaction deletes an employee record and creates a deletion log. However, it's possible for the DELETE operation to succeed and the INSERT operation to fail, in which case there is no log record for the deletion.

BEGIN TRANSACTION;

DELETE FROM employees
WHERE employee_id = 101;

INSERT INTO deletion_log (employee_id, deletion_date, reason)
VALUES (101, GETDATE(), 'Voluntary Resignation');

COMMIT TRANSACTION;

Example prompt

How can I ensure that the `DELETE` and `INSERT` operations are only performed if both succeed?

---

Source: https://docs.github.com/en/copilot/copilot-chat-cookbook/refactoring-code/fixing-database-deadlocks-or-data-integrity-issues#avoiding-data-integrity-issues