Sql-server – How to delete records with FK constraint using Delete Trigger

sql servertrigger

I'm learning MS SQL Server and trying to implement a trigger to delete records in the child table if the record in the parent table is deleted. I'm using AdventureWorksDW database provided my Microsoft.

I have two tables DimProductSubcategory and DimProduct. So if I delete a product category in DimProductSubcategory, all related records in DimProduct should also be deleted.

So I have created a trigger:

CREATE trigger on_delete_trigger
    on DimProductSubcategory
    after delete
    as
    begin
        SET NOCOUNT ON;
        DELETE FROM DimProduct WHERE (DimProduct.ProductSubcategoryKey IN (SELECT DimProduct.ProductSubcategoryKey FROM DimProductSubcategory))
    end

But when I try to delete a record in DimProductSubcategory I get:

The DELETE statement conflicted with the REFERENCE constraint "FK_DimProduct_DimProductSubcategory". 
The conflict occurred in database "AdventureWorksDW2019", table "dbo.DimProduct", column 'ProductSubcategoryKey'.

I understand the meaning of the error message, but I do not understand the reason for it. I thought the trigger was supposed to delete all child records so that I can delete the parent record without violating data integrity.

Although I'm not 100% sure I got my DELETE statement right.

So how can I implement a trigger to delete child records when a parent record is deleted?

Best Answer

The problem you originally had was that you were not referencing the deleted pseudo-table. You were essentially saying "Oh, delete subcategory 5? Ok, delete all products that have a subcategory!"

I think you want this in your trigger:

DELETE p
  FROM dbo.DimProduct AS p
  INNER JOIN deleted AS d
  ON p.ProductSubcategoryKey = d.ProductSubcategoryKey;

I still don't believe you want to delete every product that belongs to a subcategory you are removing, but hopefully this is just an exercise and not real business logic you want to implement.

Related Solutions

Sql-server – Passing info on who deleted record onto a Delete trigger

Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Yes: by using a very cool (and under utilized feature) called CONTEXT_INFO. It is essentially session memory that exists in all scopes and is not bound by transactions. It can be used to pass info (any info--well, any that fits into the limited space) to triggers as well as back and forth between sub-proc / EXEC calls. And I have used it before for this exact same situation.

Context info is a VARBINARY(128)
Set via: SET CONTEXT_INFO
Get via: CONTEXT_INFO()

Test with the following to see how it works. Notice that I am converting to CHAR(128) before the CONVERT(VARBINARY(128), ... This is to force blank-padding to make it easier to convert back to VARCHAR when getting it out of CONTEXT_INFO() since VARBINARY(128) is right-padded with 0x00s.

SELECT CONTEXT_INFO();
-- Initially = NULL

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
                          );
SET CONTEXT_INFO @EncodedUser;

SELECT CONTEXT_INFO() AS [RawContextInfo],
       RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];

Results:

0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!

PUTTING IT ALL TOGETHER:

The app should call a "Delete" stored procedure that passes in the UserName (or whatever) that is deleting the record. I assume this is already the model being used since it sounds like you are already tracking Insert and Update operations.

The "Delete" stored procedure does:

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), @UserName)
                          );
SET CONTEXT_INFO @EncodedUser;

-- DELETE STUFF HERE

The audit trigger does:

-- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
   SELECT del.ID, COALESCE(
                     LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
                     '<unknown>')
   FROM DELETED del;

Please note that, as @SeanGallardy pointed out in a comment, due to other procedures and/or ad hoc queries deleting records from this table, it is possible that either:
- CONTEXT_INFO has not been set and is still NULL:
  
  For this reason I have updated the above INSERT INTO AuditTable to use a COALESCE to default the value. Or, if you don't want a default and require a name, then you could do something similar to:
```
DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);

IF (@UserName IS NULL)
BEGIN
   ROLLBACK TRAN; -- cancel the DELETE operation
   RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
END;

-- use @UserName in the INSERT...SELECT
```
- CONTEXT_INFO has been set to a value that is not a valid UserName, and hence might exceed the size of the AuditTable.[UserWhoMadeChanges] field:
  
  For this reason I added a LEFT function to ensure that whatever is grabbed out of CONTEXT_INFO will not break the INSERT. As noted in the code, you just need to set the 50 to the actual size of the UserWhoMadeChanges field.

UPDATE FOR SQL SERVER 2016 AND NEWER

SQL Server 2016 added an improved version of this per-session memory: Session Context. The new Session Context is essentially a hash table of Key-Value pairs with the "Key" being of type sysname (i.e. NVARCHAR(128) ) and the "Value" being SQL_VARIANT. Meaning:

There is now a separation of values so less likely to conflict with other uses
You can store various types, no longer needing to worry about the odd behavior when getting the value back out via CONTEXT_INFO() (for details, please see my post: Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?)
You get a lot more space: 8000 bytes max per "Value", up to 256kb total across all keys (compared to the 128 bytes max of CONTEXT_INFO)

For details, please see the following documentation pages:

Mysql – trigger to delete records with same value from another table after inserting record in another table

Try this:

DELIMITER $$
CREATE TRIGGER tgr_1 AFTER INSERT ON abnormal
FOR EACH ROW
BEGIN
  DELETE FROM normal where id = NEW.id;
END $$

Best Answer

Related Solutions

Sql-server – Passing info on who deleted record onto a Delete trigger

Mysql – trigger to delete records with same value from another table after inserting record in another table

Related Question