Delete from multiple tables in Sql Server

BEGIN TRAN

  -- create temporary table for deleted IDs
  CREATE TABLE TestDelete (
    Id INT NOT NULL PRIMARY KEY
  )

  -- save IDs of master table records (you want to delete) to temporary table  
  INSERT INTO TestDelete(Id)
  SELECT DISTINCT mt.tblMasterID
  FROM tbltblMaster mt
  WHERE mt.tblMasterID=3

  -- delete from first detail table using join syntax
  DELETE d
  FROM tblFirst D
  INNER JOIN TestDelete X
    ON D.tblFirstID = X.Id


  -- delete from second detail table using IN clause
  DELETE FROM tblSecond
  WHERE SecondID IN (
    SELECT X.Id
    FROM TestDelete X
  )


  -- and finally delete from master table
  DELETE d
  FROM tblMaster D
  INNER JOIN TestDelete X
    ON D.MasterID = X.Id

  -- do not forget to drop the temp table
  DROP TABLE TestDelete

COMMIT