/*Script para depurar Localidades*/

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Depurando Colonias'
GO
delete pld_cod_colonia
where idciudad in (select y.idciudad from pld_cod_ciudad y
where y.idMunicipio in (select x.idmunicipio
from pld_cod_municipio x
where x.idEntidad_Federativa in (select z.idEntidad_Federativa
from pld_cod_entidad_federativa z
where z.idpais=3)))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Depurando Ciudades'
GO
delete pld_cod_ciudad
where idMunicipio in (select x.idmunicipio
from pld_cod_municipio x
where x.idEntidad_Federativa in (select z.idEntidad_Federativa
from pld_cod_entidad_federativa z
where z.idpais=3))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Depurando Municipios'
GO
delete pld_cod_municipio
where identidad_federativa in (select x.identidad_federativa
from pld_cod_entidad_federativa x
where x.idpais=3)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Depurando Localidades'
GO
delete pld_cod_localidad
where identidad_federativa in (select x.identidad_federativa
from pld_cod_entidad_federativa x
where x.idpais=3)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Depurando Entidades Federativas'
GO
delete pld_cod_entidad_federativa
where idPais =3
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Depurando Paises'
GO
delete pld_cod_pais
where idpais=3
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT N'Se depuraron las localidades Exitosamente'
COMMIT TRANSACTION
END
ELSE PRINT N'Ocurrio un error al depurar Localidades'
GO
DROP TABLE #tmpErrors
GO