Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 trigger manages start & end expiration dates

Author  Topic 

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2010-04-07 : 13:00:52
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-07 : 15:47:55
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

87 Posts

Posted - 2010-04-08 : 15:38:26
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

38200 Posts

Posted - 2010-04-08 : 20:05:32
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
   

- Advertisement -