Trigger referring twice to a table doesn't work properly

Question

Post reply

Trigger referring twice to a table doesn't work properly

petitpere

SSCrazy

Points: 2520
More actions
May 22, 2013 at 6:53 am

#273015

Hi all,
I have a problem with a trigger and I hope some expert can help me on this ...
This is the trigger
ALTER TRIGGER [dbo].[updt_order_date] ON [dbo].[FUND_MANAGEMENT_DETAILS]
after insert
AS
BEGIN
set nocount on
declare @dt datetime
select @dt = getdate()
update fmd
set order_date =
(case f.pricing_frequency
when 2 then-- weekly
(selectmin(c.dt)
fromcalendar cc,
calendar c
where1 = 1
and cc.dt = @dt
and c.dw_lissia = f.PRICING_DAY
and c.id_fisa_bsn_day >= cc.id_fisa_bsn_day + f.FWD_PRICE_REPORT_DAYS
)
else ''
end)
fromFUND_MANAGEMENT_DETAILS fmd
inner join inserted i on i.FMD_ID = fmd.FMD_ID
inner join FUNDS f on f.FDS_ID = fmd.FUND
END
This code doesn't work! The case "when 2" is never executed although it should be for some rows.
However, if I rem out all reference to calendar cc, which gives following code, it works; meaning the results are wrong but the "case 2 " gets executed.
ALTER TRIGGER [dbo].[updt_order_date] ON [dbo].[FUND_MANAGEMENT_DETAILS]
after insert
AS
BEGIN
update fmd
set order_date =
(case f.pricing_frequency
when 2 then-- weekly
(selectmin(c.dt)
from--calendar cc,
calendar c
where1 = 1
--and cc.dt = @dt
and c.dw_lissia = f.PRICING_DAY
and c.id_fisa_bsn_day >= 98 + f.FWD_PRICE_REPORT_DAYS
)
else ''
end)
fromFUND_MANAGEMENT_DETAILS fmd
inner join inserted i on i.FMD_ID = fmd.FMD_ID
inner join FUNDS f on f.FDS_ID = fmd.FUND
END
I tried many scenarii since yesterday, but none of them succeeds and to be honest I run against the wall now.
I'd be grateful if anybody with a good knowledge about triggers could help me.
Thanks
rv
EDIT: to paste missing code

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

you must not be pasting the whole code;

nowhere do i see the variable @dt defined, let alone set to a date/datetime value.

it doesn't pass basic syntax checking because of the variable definition missing.

if you are assigning @dt soemwhere else to getdate(), it's probably the problem where getdate has the time down to teh millisecond, so your join criteria is wrong...

ie and cc.dt = @dt doesn't match if '2013/05/22' = '2013-05-22 09:06:14.780'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

petitpere SSCrazy Points: 2520 More actions · Answer 2

Sorry, I missed the 3 first line

set nocount on

declare @dt datetime

select @dt = getdate()

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 3

The case is with table f. alias so if it is not firing then some value in f table is missing and not fulfilling, and dont see any problem with internal subquery where you are getting the Date value,

One more thing First of all what see is in your else condition you have updated it with '' which will save the date as StatDate set in your server configuration, Replace it with NULL and try.

petitpere SSCrazy Points: 2520 More actions · Answer 4

Stupid me!!!!!!!

You are right Lowell.

Here's the correction if it helps someone ...

select @dt = convert(char(10), getdate(), 111)

Sometimes you're stuck with a problem for hours and you'd better have a coffee without thinking about it rather than trying to solve the problem!

Many thanks Lowell!!!!

petitpere SSCrazy Points: 2520 More actions · Answer 5

Thanks for your answer shettybhas!

As Lowell stated, the problem was with getdate() returning a timestamp (date + hour) and the comparison was only on the date part; so it could never be equal.

Anyway thanks for your help!

Sergiy SSC Guru Points: 110206 More actions · Answer 6

rot-717018 (5/22/2013)
Here's the correction if it helps someone ...
select @dt = convert(char(10), getdate(), 111)

By all means try to avoid datetime-char-datetime conversions.

Pretty efficient performance killer.

Develop a habit to use this method:

select @dt = dateadd(dd, datediff(dd, 0, getdate()), 0)

Slight modifications to this formula will jelp you with easy rounding dates not only to days but to hour, minutes, seconds, month, weeks, years.

Or even "N hours blocks":

DECLARE @N_hours int

set @N_hours = 3

select dateadd(hh, datediff(hh, 0, getdate())/@N_hours*@N_hours, 0)

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Sergiy (5/22/2013)
rot-717018 (5/22/2013)
Here's the correction if it helps someone ...
select @dt = convert(char(10), getdate(), 111)
By all means try to avoid datetime-char-datetime conversions.
Pretty efficient performance killer.
Develop a habit to use this method:
select @dt = dateadd(dd, datediff(dd, 0, getdate()), 0)
Slight modifications to this formula will jelp you with easy rounding dates not only to days but to hour, minutes, seconds, month, weeks, years.
Or even "N hours blocks":
DECLARE @N_hours int
set @N_hours = 3
select dateadd(hh, datediff(hh, 0, getdate())/@N_hours*@N_hours, 0)

If you are interested is date routines, check this out: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

petitpere SSCrazy Points: 2520 More actions · Answer 8

Thanks Sergiy and Lynn for these further informations.

I did not know about it and did not notice any performance problem with casting the dates.

However, as this is a trgigger, I'll change the code according to your suggestions.

Again thanks to point out this problem ...

😎