After insert,Update,Delete Trigger needs to insert into same table trigger is running on

Question

After insert,Update,Delete Trigger needs to insert into same table trigger is running on

Charlottecb

Hall of Fame

Points: 3932
More actions
November 19, 2015 at 1:26 pm

#306511

Ok, SQL Server 2008.
Problem as follows... I have a table of rates which must have the date range of the rates as contiguous (no gaps between dates). I have put an After Insert, Update, Delete trigger on the table to handle this. So far so good, trigger adjusts the dates of the rows to keep the dates all nice and tight with no gaps.
However, If a new rate is inserted and has a smaller date range than an existing rate's date range - e.g the new records start is after the existing records start and the new records end is less than the existing records end, I need to shorten the date range of the original rate, insert the new rate then copy the original record and put it's start to the end of the new record with it's original end date, so in effect, the new rate has split the old rate in two.
With me so far? I have the trigger doing all this correctly apart from one thing... The problem I am having is that I can't insert the new record created from the original rate that was split. I have calculated the start and end all correctly, but the insert code refuses to fire and doesn't throw any errors...
I have tried running the insert statement by itself in a new query and it works fine but once it is in the trigger it doesn't do anything.
The only thing I can think of is that because the code is firing inside the table trigger, I cannot insert another record into the same table the trigger is on during the original insert (possibly as it would go into a loop) - is this assumption correct? If yes, can anyone think of a work around?
Short of moving all the code to the front end, I'm completely stuck on this one - any help would be most appreciated.
Many Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

You might try using an INSTEAD OF trigger instead. I've never used them, so I don't I can't really give you much more info about them.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

Thanks for the reply. Have figured it out - was a mistake in my code!

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Or you could simplify this a bit and drop the end date from your data. The EndDate could just effectively be any value that is before the next start date. It is easier to work with this kind of structure in 2012+ with LEAD and LAG but you can do it effectively in 2008 with a recursive cte. If you do that there is no chance of gaps because the end date is always calculated based on the existing data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Charlottecb Hall of Fame Points: 3932 More actions · Answer 4

That's a good idea Sean, I hadn't though of that. I will investigate it.

What is the best way to find the ID of date record I need if I only have a start date and I pass in a date that is between two records (I would need the record with the lower start date value)? I'm on 2008.

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Charlottecb (11/19/2015)
That's a good idea Sean, I hadn't though of that. I will investigate it.
What is the best way to find the ID of date record I need if I only have a start date and I pass in a date that is between two records (I would need the record with the lower start date value)? I'm on 2008.
Thanks

Not quite sure how you plan on retrieving this data by passing in a date so I used a table valued function which is pretty flexible. Here is an example of how this could work.

if OBJECT_ID('RateTest') is not null

drop table RateTest

create table RateTest

(

RateID int identity

, StartDate datetime

)

insert RateTest

select '20150101' union all

select '20150201' union all

select '20150301' union all

select '20150401' union all

select '20150501' union all

select '20150601' union all

select '20150701' union all

select '20150801' union all

select '20150901' union all

select '20151001' union all

select '20151101' union all

select '20151201';

GO

if OBJECT_ID('GetRateData') is not null

drop function GetRateData

GO

CREATE FUNCTION GetRateData

(

@FindDate datetime

) RETURNS TABLE AS RETURN

with NumberedPeriods as

(

select

RateID

, StartDate

, ROW_NUMBER() over (order by StartDate) as RowNum

from RateTest

)

select top 1

np.RateID

, np.StartDate as StartDate

, np2.StartDate as EndDate --Could add an Isnull here with a temporary end date until a rate is created

from NumberedPeriods np

left join NumberedPeriods np2 on np.RowNum = np2.RowNum - 1

where np.StartDate < @FindDate

order by np.StartDate desc

;

GO

select * from GetRateData('20150315') --should return ID 3

delete RateTest

where StartDate = '20150301'

select * from GetRateData('20150315') --should return ID 2

--notice the effective end date hasn't changed because the row for April is still the end

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Charlottecb Hall of Fame Points: 3932 More actions · Answer 6

Charlottecb

Hall of Fame

Points: 3932

November 20, 2015 at 7:59 am

#1841371

Brilliant Sean - Many thanks for this. 😀

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Charlottecb (11/20/2015)
Brilliant Sean - Many thanks for this. 😀

You are welcome. I don't know if it will work for you but it seems a lot simpler than trying to keep end dates in synch all the time. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/