SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 trigger manages start & end expiration dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cwfontan
Yak Posting Veteran

USA
87 Posts

Posted - 04/07/2010 :  13:00:52  Show Profile  Send cwfontan an AOL message  Reply with Quote
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 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

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 04/07/2010 :  15:47:55  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

cwfontan
Yak Posting Veteran

USA
87 Posts

Posted - 04/08/2010 :  15:38:26  Show Profile  Send cwfontan an AOL message  Reply with Quote
You are right and for my particular case its controled... But with the test data below how would you accomplish the same?

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.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 04/08/2010 :  20:05:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't have time to rewrite your trigger to one that can handle multiple rows and is still set-based, but here's my blog post regarding this matter: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

I haven't looked over your code, but in case it can't be changed to set-based, then the other way to handle multiple rows is to loop through the trigger table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000