BEGIN TRAN
	--Eliminar registros de ManifiestosRem que no esten en remesas
	Delete from ManifiestosRem
	where RemNroRemEmpresa not in (select RemNroRemEmpresa from remesas)
GO
	--Asignar actualizacion en cascada para la llave numremesa en tabla manifiestos rem
	ALTER TABLE dbo.ManifiestosRem ADD CONSTRAINT
		FK_ManifiestosRem_Remesas FOREIGN KEY
		(
		RemNroRemEmpresa
		) REFERENCES dbo.Remesas
		(
		RemNroRemEmpresa
		) ON UPDATE  CASCADE 
		 ON DELETE  NO ACTION 
GO	
--Crear tabla de copia de remesas
	CREATE TABLE dbo.zrem_backup
	(
	numremesa varchar(15) NOT NULL,
	IngresoId decimal(16, 2) NULL
	)  ON [PRIMARY]
	GO
	ALTER TABLE zrem_backup ADD CONSTRAINT
		PK_zrem_backup PRIMARY KEY CLUSTERED 
		(
		numremesa
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--Eliminar remesas sin radicado
	delete  from remesas where estadoreg <>1 and ingresoid=0


--Crear datos de respaldo
	select r.RemNroRemEmpresa, r.ingresoid
	INTO dbo.zrembackup
	from remesas as r left join cumplidosrem cr on r.RemNroRemEmpresa= cr.RemNroRemEmpresa
	where cr.RemNroRemEmpresa is null or cr.ingresoid = 0

	--select r.RemNroRemEmpresa, r.ingresoid, cr.RemNroRemEmpresa, cr.ingresoid
	--from remesas as r left join cumplidosrem cr on r.RemNroRemEmpresa= cr.RemNroRemEmpresa
	--where cr.RemNroRemEmpresa is null or cr.ingresoid = 0

	update Remesas set RemNroRemEmpresa =RemNroRemEmpresa + '-01' 
	where (RemNroRemEmpresa not like '%-%' and estadoreg<>1) or 
	(RemNroRemEmpresa not like '%-%' and RemNroRemEmpresa not in (SELECT RemNroRemEmpresa FROM  CumplidosRem))

ROLLBACK TRAN


CREATE trigger [dbo].[actualizarem]
 on [dbo].[CumplidosRem]
  after insert 
  as
  begin
		update c  set c.RemNroRemEmpresa = b.numremesa
		from CumplidosRem as c inner join zrembackup as b on substring (c.RemNroRemEmpresa,0, charindex ('-', c.RemNroRemEmpresa))= b.numremesa
		
		update r set r.RemNroRemEmpresa =  b.numremesa
		from Remesas as r inner join zrembackup as b on substring (r.RemNroRemEmpresa,0, charindex ('-', r.RemNroRemEmpresa))= b.numremesa
end