Insert Trigger - SQL Server Forums

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

New to SQL Server Programming

Insert Trigger

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2014-06-05 : 07:11:22

Below is a trigger which set the date to required date to Monday.
I have set up a trigger on the Ord_Header table which is working fine.

With the Ord_Detail table I am struggling. This is because I only want to up date where the OH_URGENT_FLAG <> 1 (this is in the Ord_Header table)

How can I write this trigger. I believe it imports into the Ord_Header table before the Ord_Detail.

alter TRIGGER [dbo].[udef_update_required_date_detail_to_monday_for_weekly_despatches] ON [dbo].[ORD_DETAIL]
for Insert
AS
BEGIN
SET NOCOUNT ON;

UPDATE dbo.ORD_DETAIL
SET OD_REQDATE = (CASE WHEN DATEPART (DW,inserted.OD_REQDATE) = 1 then inserted.OD_REQDATE +1
WHEN DATEPART (DW,inserted.OD_REQDATE) = 3 then inserted.OD_REQDATE +6
WHEN DATEPART (DW,inserted.OD_REQDATE) = 4 then inserted.OD_REQDATE +5
WHEN DATEPART (DW,inserted.OD_REQDATE) = 5 then inserted.OD_REQDATE +4
WHEN DATEPART (DW,inserted.OD_REQDATE) = 6 then inserted.OD_REQDATE +3
WHEN DATEPART (DW,inserted.OD_REQDATE) = 7 then inserted.OD_REQDATE +2
ELSE inserted.OD_REQDATE END)

FROM ORD_DETAIL INNER JOIN inserted ON ORD_DETAIL.OD_PRIMARY = inserted.OD_PRIMARY
--INNER JOIN ORD_HEADER on ORD_HEADER.OH_ORDER_NUMBER = INSERTED.OD_ORDER_NUMBER

WHERE
OH_URGENT_FLAG <> 1
and
INSERTED.Od_ACCOUNT in ('EMINE003','EEATN003','EEATN007','EEATN005','EEATN011','EEATN009','EEATN010','ERAVE015','ERAVE017','EHAWL003','ELYCE004','ESANC001','ETWIC004')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-05 : 08:08:39

So what is your problem?
What isn't working if you are joining the Header like you do in the comment line?

Too old to Rock'n'Roll too young to die.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2014-06-05 : 08:19:25

With that uncommented it just does nothing. It doesn't error. Which is a bit strange. Do you think what I have done should work?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-05 : 08:33:57

At first glance I think it should work...

maybe you can insert some select statements in your trigger to see/test if the given conditions are true or not...

Too old to Rock'n'Roll too young to die.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-05 : 09:33:18

I would write the update statement like this:

update ORD_DETAIL
   set OD_REQDATE=OD_REQDATE+sign((cast(OD_REQDATE as integer)-1)%7)*7-(cast(OD_REQDATE as integer)-1)%7
 where exists (select 1
                 from ORD_HEADER
                where ORD_HEADER.OH_ORDER_NUMBER=ORD_DETAIL.OD_ORDER_NUMBER
                  and ORD_HEADER.OH_URGENT_FLAG<>1
              )
   and cast(OD_REQDATE as integer)%7<>1 /* no need to update mondays */
   and OD_ACCOUNT in ('EMINE003'
                     ,'EEATN003'
                     ,'EEATN007'
                     ,'EEATN005'
                     ,'EEATN011'
                     ,'EEATN009'
                     ,'EEATN010'
                     ,'ERAVE015'
                     ,'ERAVE017'
                     ,'EHAWL003'
                     ,'ELYCE004'
                     ,'ESANC001'
                     ,'ETWIC004'
                     )

ps. This has not been syntax tested.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-05 : 09:42:19

quote:

Originally posted by bitsmed

I would write the update statement like this:

update ORD_DETAIL
   set OD_REQDATE=OD_REQDATE+sign((cast(OD_REQDATE as integer)-1)%7)*7-(cast(OD_REQDATE as integer)-1)%7
 where exists (select 1
                 from ORD_HEADER
                where ORD_HEADER.OH_ORDER_NUMBER=ORD_DETAIL.OD_ORDER_NUMBER
                  and ORD_HEADER.OH_URGENT_FLAG<>1
              )
   and cast(OD_REQDATE as integer)%7<>1 /* no need to update mondays */
   and OD_ACCOUNT in ('EMINE003'
                     ,'EEATN003'
                     ,'EEATN007'
                     ,'EEATN005'
                     ,'EEATN011'
                     ,'EEATN009'
                     ,'EEATN010'
                     ,'ERAVE015'
                     ,'ERAVE017'
                     ,'EHAWL003'
                     ,'ELYCE004'
                     ,'ESANC001'
                     ,'ETWIC004'
                     )

ps. This has not been syntax tested.

"inserted" isn't involved so the trigger would always update each entry in the table and not only the just inserted rows...

Too old to Rock'n'Roll too young to die.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-05 : 10:05:29

quote:
Originally posted by webfred

"inserted" isn't involved so the trigger would always update each entry in the table and not only the just inserted rows...

Right, I forgot we were dealing with a trigger.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2014-06-05 : 10:08:41

Thanks Guys, I have done something along those lines. Shown below. The only thing which happens now is it doesn't update the line which is being inserted?

AFTER Insert
AS
BEGIN
SET NOCOUNT ON;

UPDATE dbo.ORD_DETAIL
SET OD_REQDATE = (CASE WHEN DATEPART (DW,OD_REQDATE) = 1 then OD_REQDATE +1
WHEN DATEPART (DW,OD_REQDATE) = 3 then OD_REQDATE +6
WHEN DATEPART (DW,OD_REQDATE) = 4 then OD_REQDATE +5
WHEN DATEPART (DW,OD_REQDATE) = 5 then OD_REQDATE +4
WHEN DATEPART (DW,OD_REQDATE) = 6 then OD_REQDATE +3
WHEN DATEPART (DW,OD_REQDATE) = 7 then OD_REQDATE +2
ELSE OD_REQDATE END)

-- select DATEPART(ss, GETDATE()), DATEPART(ss, OH_DATE_PUTIN ), OH_DATE_PUTIN

FROM ORD_DETAIL
INNER JOIN ORD_HEADER on ORD_HEADER.OH_ORDER_NUMBER = OD_ORDER_NUMBER

WHERE
OH_URGENT_FLAG <> 1
and OH_STATUS <> 2
and OD_REQDATE <> OH_REQUIREDDATE
and
Od_ACCOUNT in ('EMINE003','EEATN003','EEATN007','EEATN005','EEATN011','EEATN009','EEATN010','ERAVE015','ERAVE017','EHAWL003','ELYCE004','ESANC001','ETWIC004')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources