2

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends.

Aisle Table:

Aisle char(4)
Start_Node int not null
End_Node int not null
...

This table would have foreign keys referencing the Node table (deleting on cascade), but this is apparently not possible (multiple cascade paths?) so I have to enforce this rule myself in a trigger on the Node Table...which looks like so:

Node_ID int
x float
y float
Aisle char(4) [MAY BE NULL]

Now, the trigger on the node table does some data integrity stuff, and I'm pretty sure a trigger is the right place to do it since I absolutely cannot think of a situation where I wouldn't want the trigger to fire -- but it doesn't always fire...

If I manually delete an entry from the Aisle table to which rows in Node reference, the deletion cascades to the Node table and the trigger definitely fires, as expected.

But if I delete a record from the Node table to which Aisle rows reference, the trigger on the Node table enforces the pseudo-foreign key constraint in Aisle (deleting rows in Aisle by design), which should then cascade the deletions back to Node and call the trigger again (by design), but I have verified multiple times that the trigger never gets called after the cascade occurs.

What's going on here? I'm clearly missing some detail on how triggers and FKs interact...for the record, nested triggers are active, I'm using an After Delete trigger, and I'm definitely no DBA.

  • 1
    Considered an instead of delete trigger? – Aaron Bertrand Oct 29 '14 at 2:07
  • I'm curious as to why you wouldn't just use a foregin key on Aisle and CASCADE ON DELETE? Are you doing something else in the trigger? – Steve Mangiameli Oct 31 '14 at 13:01

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.