the trigger on insert checks previous expiration dates to new effdates and updates the previous expiration.. it also checks future effdates and sets the new expiration date if less than.. i had to create it to start and stop prices we charge at different times.. Hope it helps.USE [YOURDBNAME]GO/****** Object: Table [dbo].[Test] Script Date: 04/07/2010 11:59:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Test]( [key] [int] IDENTITY(1,1) NOT NULL, [id] [int] NULL, [effdate] [datetime] NULL, [expdate] [datetime] NULL) ON [PRIMARY]insert into TestValues (1,'2010-01-01',NULL)insert into TestValues (1,'2010-02-01',NULL)insert into TestValues (1,'2010-02-15',NULL)insert into TestValues (1,'2010-04-1',NULL)insert into TestValues (1,'2010-09-1',NULL)insert into TestValues (1,'2010-09-3',NULL)insert into TestValues (1,'2010-05-1',NULL)insert into TestValues (2,'2010-01-15',NULL)
USE [YOURDBNAME]GO/****** Object: Trigger [dbo].[trg_test] Script Date: 04/07/2010 08:34:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE trigger [dbo].[trg_test]on [dbo].[Test]after insertasBeginDeclare @tblID intDeclare @count intDeclare @newEffective datetimeDeclare @lastExpiration datetimeDeclare @futureEffective datetimeDeclare @msg_var varchar(100)Declare @msg_var2 varchar(255)Select @tblID = [key] from insertedSelect @newEffective = effdate from insertedselect @lastExpiration = max(t.expdate) from test t inner join inserted on t.id = inserted.id and t.effdate <= @newEffective AND t.[key] <> @tblIDSelect @futureEffective = min(t.effdate) from test t inner join inserted on t.id = inserted.id and t.effdate > @newEffectiveSelect @count = count(t.[key]) FROM test t inner join inserted on t.id = inserted.id AND t.effDate = @newEffective AND t.[key] <> @tblIDSet @msg_var = convert(varchar(20), @newEffective, 101)Set @msg_var2 = @msg_var + ' must be > ' + convert(varchar(20), @lastExpiration, 101)--print @lastExpiration--print @countIF @count > 0 --if effDates=newEff Begin RAISERROR (N'Effective date conflicts choose another effdate %s', 10, 1, @msg_var) ROLLBACK TRANSACTION RETURN endIf @lastExpiration < getDate() AND @lastExpiration > @newEffective Begin RAISERROR (N'Effective date %s', 10, 1, @msg_var2) ROLLBACK TRANSACTION RETURN endBegin 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 endIf @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 endend