Control de registros duplicados sin utilizar índices

[ContraVeneno]

Que tal

Tengo una Tabla para el tipo de cambio, básicamente con los campos:
Departamento: Int
TipoCambio: Money
Fecha: DateTime
Estatus: Bit

La idea es que por cada departamento, debo de tener un tipo de cambio que este activo, guardando el historial de tipos de cambio que ha tenido cada departamento.

Ejemplo: Esto es correcto

Departamento TipoCambio Fecha Estatus
-----------------------------------------------
1 5.00 1994-01-01 0
1 10.00 2008-01-01 0
1 13.00 2009-01-01 1
2 13.00 2009-01-01 1
2 10.00 2008-01-01 0
3 13.00 2009-01-01 1


Esto NO es correcto ya que para el mismo departamento, no debería tener dos tipos de cambio con estatus activo.

Departamento TipoCambio Fecha Estatus
-----------------------------------------------
1 5.00 1994-01-01 0
1 10.00 2008-01-01 1
1 13.00 2009-01-01 1
2 13.00 2009-01-01 1
2 10.00 2008-01-01 0
3 13.00 2009-01-01 1


Mi primera idea era usar un índice único por departamento y por estatus, pero entonces no podría tener el historial (varios tipos de cambio con estatus 0).

Entonces: ¿Cómo puedo controlar que exista solo un tipo de cambio activo por cada departamento?


Gracias.

P.D. Todo se tendría que hacer desde la base de datos SQL Server.

[marcoszorrilla]

Select Count(Departamento) as dep
From Contra
Where estatus = '1'
Group by departamento

Esta sería la idea inicial quizás habría que agregarle al Where el departamento que estás tratando dar de alta para que solamente compruebe ese y si devuelve 1 es que ya existe y controlando el evento beforeInsert o el que proceda evitar el alta si ya existe algún registro.

Un Saludo.

[sinalocarlos]

Una duda, siempre tiene que haber un tipo de cambio activo por departamento? o puede llegarse a dar que todos esten deshabilitados?

[ContraVeneno]

... controlando el evento beforeInsert ...
Un Saludo.

El detalle maese Marcos, es que esto lo quiero controlar desde la base de datos, no desde un programa. :o

Una duda, siempre tiene que haber un tipo de cambio activo por departamento? o puede llegarse a dar que todos esten deshabilitados?

Siempre debe de haber un tipo de cambio activo por departamento. Ahora, que sería un poco complicado controlar una instrucción "Delete" directa a la base de datos por lo que no podría ponerme tan exigente en este aspecto; en Sí, lo único que quiero controlar es que cuando se realize la siguiente instrucción, me regrese solo un registro o ninguno:
Select TipoCambio
From Tabla
Where Depto = 1


Estaba intenando utilizar "CHECK CONSTRAINTS" (Restricciones Check (http://msdn.microsoft.com/es-es/library/ms188258.aspx)), como mencionan en la ayuda de SQL:

CREATE TABLE TipoCambioDep (Departamento int, TipoCambio Money, Status Bit)
GO
CREATE FUNCTION CheckFnctn(@Departamento int)
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*) FROM TipoCambioDep
Where Departamento = @Departamento and Status <> 0
RETURN @retval
END
GO
ALTER TABLE TipoCambioDep
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn(Departamento) <= 1 )
GO

----------------------------
Edito:
Esto me funciona exactamento como quiero, si la instrucción es un INSERT... es decir no puedo insertar otro tipo de cambio activo para un departamento que ya tiene un tipo de cambio activo... pero sí puedo hacer un UPDATE a esos registros y ponerlos todos como activo... :(
----------------------------


pero no logro hacer que funcione (completamente)...

[roman]

El detalle maese Marcos, es que esto lo quiero controlar desde la base de datos, no desde un programa.

Pero, quizá Marcos se refiera a un trigger BEFORE INSERT. ¿No serviría algo así?

// Saludos

[Neftali [Germán.Estévez]]

Otra opción sería (si te es posible y no es muy complicado) es dejar esta tabla como histórico y tener otra que te marque el activo. En ese caso, lo tendrías controlado con el índice.

[ContraVeneno]

Cierto, cierto, con un trigger o disparador como dijo maese Roman y con la ayuda de maese Marcos:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER RevisaStatus
ON TipoCambioDep
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
Declare @Valor int
Set @Valor = 0

Select @Valor = IsNull(Count(*), 0)
From TipoCambioDep T
Inner Join Inserted I on T.Departamento = I.Departamento
Where T.Status <> 0

If @Valor > 1 Begin
RAISERROR ('Ya existe un registro activo para esta tabla', 16, 1)
ROLLBACK TRANSACTION
end
END
GO


Con eso obtengo el resultado que estamos buscando. Y a menos que alguien diga que me falta o falla algo, con esto lo tenemos solucionado.

Muchas gracias a todos por sus aportaciones.

[AzidRain]

MMM Una duda, supongamos que voy a capturar un nuevo tipo de cambio por lo que pasará a ser el nuevo "activo". En que momento se "desactiva" el ¿Anterior? ¿No estará mal planteada tu regla de negocio?

Yo creo que sería así:

1.- Solo se permite un tipo de cambio activo por departamento
2.- El último tipo de cambio insertado siempre será el activo (ya que los anteriores solo quedan como historico.

Basado en eso, creo que el trigger tendría que ser before insert para desactivar el que estuviera activo para ese departamento y luego realizar la inserción del tipo de cambio nuevo dejándolo como activo.

[ContraVeneno]

ya, muy válido tu comentario... así que con algunos cambios y unos retoques aquí y allá, me queda lo siguiente, que me funciona a la perfección:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[triRevisaStatusTipoCambio]
ON [dbo].[TipoCambioDep]
INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @Valor int
Declare @NewStatus Bit

Set @Valor = 0

Select @NewStatus = Status
From Inserted

Select @Valor = IsNull(Count(*), 0)
From TipoCambioDep T
Inner Join Inserted I on T.Departamento = I.Departamento
Where T.Status <> 0

If (@Valor >= 1) and (@NewStatus <> 0) Begin
Update TipoCambioDep
Set Status = 0
From TipoCambioDep T
Inner Join Inserted I on T.Departamento = I.Departamento
Where T.Status <> 0
end

Insert into TipoCambioDep(Departamento, TipoCambio, Status)
Select Departamento, TipoCambio, Status
From Inserted

END
-------------------UPDATE -------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER truRevisaStatusTipoCambio
ON TipoCambioDep
INSTEAD OF UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @OldID INT
DECLARE @NewID INT
Declare @NewStatus Bit

SELECT @OldID = Departamento FROM DELETED

Select @NewID = I.Departamento, @NewStatus = I.Status
From Inserted I

Declare @Valor int

Set @Valor = 0

Select @Valor = IsNull(Count(*), 0)
From TipoCambioDep T
Inner Join Inserted I on T.Departamento = I.Departamento
Where T.Status <> 0

If (@Valor >= 1) and (@NewStatus <> 0) Begin
RAISERROR ('Ya existe un registro activo para esta tabla', 16, 1)
ROLLBACK TRANSACTION
Return
end

If (@Valor = 1) and (@NewStatus = 0) Begin
RAISERROR ('Debe existir al menos un registro activo.', 16, 1)
ROLLBACK TRANSACTION
Return
end

Update TipoCambioDep
Set Departamento = I.Departamento, TipoCambio = I.TipoCambio, Status = I.Status
From TipoCambioDep T
inner Join Inserted I on I.Departamento = @NewID
Where T.Departamento = @OldID
END
GO


Faltaría validar la parte del "DELETE", pero eso lo haremos en la próxima versión :D:D.

Nuevamente, a menos que alguien tenga algo que aportar o algo que señalar, quedaría resuelto.

Gracias a todos.