USE [YOURDBNAME]
GO
/****** Object:  Table [dbo].[Test]    Script Date: 04/07/2010 11:59:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
	[key] [int] IDENTITY(1,1) NOT NULL,
	[id] [int] NULL,
	[effdate] [datetime] NULL,
	[expdate] [datetime] NULL
) ON [PRIMARY]


insert into Test
Values (1,'2010-01-01',NULL)

insert into Test
Values (1,'2010-02-01',NULL)

insert into Test
Values (1,'2010-02-15',NULL)

insert into Test
Values (1,'2010-04-1',NULL)

insert into Test
Values (1,'2010-09-1',NULL)

insert into Test
Values (1,'2010-09-3',NULL)

insert into Test
Values (1,'2010-05-1',NULL)

insert into Test
Values (2,'2010-01-15',NULL)

USE [YOURDBNAME]
GO
/****** Object:  Trigger [dbo].[trg_test]    Script Date: 04/07/2010 08:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [dbo].[trg_test]
on [dbo].[Test]
after insert
as
Begin
Declare @tblID int
Declare @count int
Declare @newEffective datetime
Declare @lastExpiration datetime
Declare @futureEffective datetime
Declare @msg_var varchar(100)
Declare @msg_var2 varchar(255)

Select @tblID = [key] from inserted
Select @newEffective = effdate from inserted

select @lastExpiration = max(t.expdate) 
from test t inner join inserted on 
			t.id = inserted.id and 
			t.effdate <= @newEffective AND 
			t.[key] <> @tblID

Select @futureEffective = min(t.effdate) 
from test t inner join inserted on 
			t.id = inserted.id and 
			t.effdate > @newEffective

Select @count = count(t.[key]) 
FROM test t inner join inserted on 
			t.id = inserted.id AND 
			t.effDate = @newEffective AND 
			t.[key] <> @tblID



Set @msg_var = convert(varchar(20), @newEffective, 101)
Set @msg_var2 = @msg_var + ' must be > ' + convert(varchar(20), @lastExpiration, 101)
--print @lastExpiration
--print @count
IF @count > 0 --if effDates=newEff 
	Begin
		RAISERROR (N'Effective date conflicts choose another effdate %s', 10, 1, @msg_var) 
		ROLLBACK TRANSACTION
		RETURN
	end

If @lastExpiration < getDate() AND @lastExpiration > @newEffective
	Begin
		RAISERROR (N'Effective date %s', 10, 1, @msg_var2) 
		ROLLBACK TRANSACTION
		RETURN
	end

Begin
		update Test set expdate = dateadd(dd,-1,@newEffective) -- '2010-2-23'
		From Test t inner join inserted i on t.id = i.id 
		WHERE (t.effDate <= @newEffective 
			AND (t.expDate >= @newEffective OR t.expdate IS NULL)
			AND (t.expDate >= GETDATE() OR t.expdate IS NULL))
			AND t.[key] <> @tblID 

end
If @futureEffective IS NOT NULL 
	Begin		
			update Test set expdate = dateadd(dd,-1,@futureEffective) -- '2010-2-23'
			From Test t inner join inserted i on t.[key] = @tblID
			WHERE (t.[key] = @tblID) AND (t.expDate >= GETDATE() OR t.expDate IS NULL)
			--print @tblID
			--print @futureEffective
	end
end

USE yourDB

insert into test (ID,effDate,expdate)
SELECT 1,'2010-02-01',NULL
UNION ALL
SELECT 1,'2010-02-15',NULL
UNION ALL
SELECT 1,'2010-04-1',NULL
UNION ALL
SELECT 1,'2010-09-1',NULL
UNION ALL
SELECT 1,'2010-05-1',NULL
UNION ALL
SELECT 2,'2010-01-15','2010-02-28'

quote:

---

Originally posted by webfred

I am sorry but I think you will get problems if INSERTED contains more than one row at a time (and that is possible).

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---