sql server - is this correct this intead of delete trigger? and other questions about tirggers -

i need to delete children of table when delete parent. common order/details example.

i use instead trigger:

create trigger trg_oderdelete    on  oders    instead of delete  begin     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;      -- insert statements trigger here     delete details idorder in(select deleted.idorder deleted)     delete orders idorder in(select deleted.idorder deleted) end go 

first, try fist delete statement, delete de details, not orders, added second delete delete order too.

my doubt if correct or not. mean if trigger executed when delete order, why have added delete statement in trigger delete order execute trigger?

i know this:

1.- transaction? mean if delete details , reason order can't deleted, details not deleted?

2.- transaction avoid added new details when delete ordener? imagine want delete order, in trigger executed first delete, details, before executed second delete, order, other user try added new detail. detail added or not because order blocked in transaction?

thanks.

why have added delete statement in trigger delete order execute trigger?

because it's instead of trigger - you've told sql server you'll take responsibility performing delete.

is transaction? mean if delete details , reason order can't deleted, details not deleted?

the outer delete caused trigger fire either in transaction or have caused 1 start. if error occurs causes trigger abort between 2 deletes, and caller has proper strategy dealing errors (e.g. xact_abort on; or they're using try/catch or checking @@error , calling rollback) first delete rolled when transaction rolls back.

if, otoh, they're ignoring errors , committing transactions anyway, end details rows still having been deleted.

this transaction avoid added new details when delete ordener? imagine want delete order, in trigger executed first delete, details, before executed second delete, order, other user try added new detail. detail added or not because order blocked in transaction?

the delete against details have taken exclusive lock on table. nobody going able execute insert/update/delete against table until transaction commits.