Will a delete then insert within a transaction cascade? I had a question about what the expected and actual behaviour for the following scenario is.

The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an id column which is reference by several other tables using foreign keys. If I set the foreign key ON DELETE action to CASCADE, obviously this will delete rows in the other tables if I issue a single DELETE FROM [table1] command on this table. However, what would happen if I were to delete all the rows, and then re-insert the same rows with the same IDs under the same transaction? Will this trigger the cascade mid-transaction, or will the foreign-key reconciliation happen once I've called commit?

Obviously, I'm thinking SQL Server here, but I'm wondering if this behaviour is consistent across other DBs as well.

In the event that the cascade is triggered even in the middle of the transaction, what would be the best way to go about managing foreign keys and relationships with a table which is completely cleared and re-loaded every day?
I'm only familiar with SQL Server:

Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.

If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE or, preferably MERGE) instead of blowing everything away and starting from scratch.

I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.