Simple trigger: maybe? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Simple trigger: maybe?

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-20 : 15:24:29

Here we go again!
I have a table, PS_TKT_HDR, with a column SHIP_DAT(datetime).
I have a child table, PS_TKT_LIN, with a column ITEM_SHIP_DAT (datetime). When a user enters a new order, we would like to put a date into SHIP_DAT and have it update ITEM_SHIP_DAT in PS_TKT_LIN for each rowset that is connected to the ticket header. The key columns between PS_TKT_HDR and PS_TKT_LIN are TKT_NO, STA_ID, and STR_ID. This part is easy, but there is a catch! IF SHIP_DAT is blank, we don't want it to change ITEM_SHIP_DAT. Also, if a user changes SHIP_DAT at a later time, and the OLD value for SHIP_DAT DID NOT MATCH ITEM_SHIP_DAT, then we DON'T want it to change ITEM_SHIP_DAT either. Any idea how this would look in a triiger? Can this even be done this way?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 15:40:26

So how far did you get on writing the trigger? Please post what you have so far.

Tara

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-20 : 16:13:22

Sorry Tara!
I actually haven't written anything yet. I got asked for this, then got thinking about it, and the got
CONFUSED!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 16:21:07

Where did you get stuck?

Tara

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-20 : 16:24:14

Basically,
It would be something like this:

create trigger tr_Itemshipdat
on PS_TKT_HDR
for insert, update
as

update tl
set tl.ITEM_SHIP_DAT = th.SHIP_DAT
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID= tl.STA_ID
and i.TKT_NO=tl.TKT_NO
inner join ps_tkt_hdr th
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO

But the rest has got me a little puzzled...

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-20 : 16:31:41

I assume we need to write this out more like a proc, but I'm not sure how. Seems to me that we need to look at the date columns as a variable and then do our work from there...
Or am I off base on this?

There's never enough time to type code right,
but always enough time for a hotfix...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 16:38:45

In order for us to help, we'll need to see the table structures of all tables involved in the form of CREATE TABLE statements, sample data to illustrate what the tables look like in the form of INSERT INTO statements, and the expected result using this sample data after the trigger fires. So basically we need it illustrated in the form of DDL and DML so that we can play with it on our machines.

Tara

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-20 : 16:56:44

ok Tara,
I can write that up for you. Do you want ALL of the columns in each table, or just the relevant ones? These tables have quite a few columns in them...
Also, how much sample data would you like? Couple o' rows?

There's never enough time to type code right,
but always enough time for a hotfix...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 16:57:33

Just the relevant columns in the tables and just enough rows to illustrate what you want.

Tara

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-20 : 17:17:02

PS_TKT_HDR table:

create table PS_TKT_HDR
(
STR_ID varchar(10), STA_ID varchar(10),TKT_NO varchar(15),
CONSTRAINT
	PK_PS_TKT_HDR PRIMARY KEY CLUSTERED 
	(
	STR_ID, STA_ID,
	TKT_NO),
SHIP_DAT datetime (8)
)

PS_TKT_LIN table:

create table PS_TKT_LIN
(
STR_ID varchar(10), STA_ID varchar(10),TKT_NO varchar(15), SEQ_NO int (4),
CONSTRAINT
	PK_PS_TKT_LIN PRIMARY KEY CLUSTERED 
	(
	STR_ID, STA_ID,
	TKT_NO, SEQ_NO),
ITEM_SHIP_DAT datetime (8)
)

Insert for HDR:

INSERT INTO PS_TKT_HDR(STR_ID,STA_ID,TKT_NO,SHIP_DAT)
VALUES '1','1','555','08/01/04')

Insert for LIN:

INSERT INTO PS_TKT_LIN(STR_ID,STA_ID,TKT_NO,SEQ_NO,ITEM_SHIP_DAT)
VALUES '1','1','555','1','08/01/04'

This only puts one line in for the PS_TKT_LIN table, but I don't think we need more to get this working, we just need to hit all lines WHERE the HDR table and LIN table link. Basically what has to happen is:
A) If the HDR SHIP_DAT is NULL, don't modify the matching ITEM_SHIP_DAT date(s).
B) If the HDR SHIP_DAT is not NULL, modify the ITEM_SHIP_DAT(s).
C) If the HDR SHIP_DAT gets changed, and it DID MATCH(the original value before the change) the ITEM_SHIP_DAT(s), change the ITEM_SHIP_DAT(s) as well.
D) If the HDR SHIP_DAT gets chanded, and it DID NOT MATCH the ITEM_SHIP_DAT(s), leave the ITEM_SHIP_DAT(s) alone.

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-21 : 10:24:22

Hey Tara,
Does this look like it covers all the bases? maybe I am making this harder than it needs to be..

update tl
if i.ship_dat is not null
and th.SHIP_DAT=tl.ITEM_SHIP_DAT
set tl.ITEM_SHIP_DAT = th.SHIP_DAT
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID= tl.STA_ID
and i.TKT_NO=tl.TKT_NO
inner join ps_tkt_hdr th
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-21 : 11:00:49

One more time:

update tl
set tl.ITEM_SHIP_DAT = th.SHIP_DAT
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID= tl.STA_ID
and i.TKT_NO=tl.TKT_NO
inner join ps_tkt_hdr th
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO
where i.ship_dat is not null
and th.SHIP_DAT=tl.ITEM_SHIP_DAT

is this more like it?

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-21 : 12:06:33

Yet another revelation:


request for item ship date trigger:

A) If the HDR SHIP_DAT is NULL, don't modify the matching ITEM_SHIP_DAT date(s).
B) If the HDR SHIP_DAT is not NULL, modify the ITEM_SHIP_DAT(s).
C) If the HDR SHIP_DAT gets changed, and it DID MATCH(the original value before the change) the ITEM_SHIP_DAT(s), change the ITEM_SHIP_DAT(s) as well.
D) If the HDR SHIP_DAT gets changed, and it DID NOT MATCH the ITEM_SHIP_DAT(s), leave the ITEM_SHIP_DAT(s) alone.


create trigger tr_Itemshipdat
on PS_TKT_HDR
for insert, update
as
set nocount on
update tl

set tl.ITEM_SHIP_DAT = th.SHIP_DAT
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID= tl.STA_ID
and i.TKT_NO=tl.TKT_NO
inner join ps_tkt_hdr th
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO
--requirement (A) is satisifed below as null value is ignored--
--below satisfies requirement (B) also, but causes problems for requirement (D), and there is no filtering for(C)--
where i.ship_dat is not null
set nocount off

Is this of any help?

There's never enough time to type code right,
but always enough time for a hotfix...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-21 : 12:19:42

You won't be able to do it all in one update statement, I believe. You are going to need to check the inserted table against the detail table to determine what to do. You'll probably need 3 IFs in here. Just don't use variables as that'll only work for the last row.

Tara

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-21 : 12:47:27

I keep thinking the same thing, but every time I start an IF statement I get stuck. It just doesn't seem right. Now check out this:

create trigger tr_Itemshipdat
on PS_TKT_HDR
for insert, update
as
set nocount on
update tl

set tl.ITEM_SHIP_DAT = th.SHIP_DAT
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID= tl.STA_ID
and i.TKT_NO=tl.TKT_NO
inner join ps_tkt_hdr th
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO

--requirement (A) is satisifed below as null value is ignored, and (B) is satisfied also --
where I.SHIP_DAT IS NOT NULL
-- (C) is satisfied below; (D) is satisfied also --
AND I.SHIP_DAT<>TH.SHIP_DAT
AND TH.SHIP_DAT=TL.ITEM_SHIP_DAT
set nocount off

does this look any better? I keep thinking it needs multiple IF statements as well, but damned if I can get it to go!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-21 : 12:52:56

Hey Tara,
Here is an option another coder I know has sent. Check it out:

update tl set
tl.ITEM_SHIP_DAT = 
case 
when th.SHIP_DAT is null 
then tl.ITEM_SHIP_DAT
when th.SHIP_DAT is not null and tl.ITEM_SHIP_DAT is null 
then th.SHIP_DAT 
when th.SHIP_DAT is not null and d.SHIP_DAT = tl.ITEM_SHIP_DAT
then th.SHIP_DAT
when th.SHIP_DAT is not null and d.SHIP_DAT <> tl.ITEM_SHIP_DAT
then tl.ITEM_SHIP_DAT
from PS_TKT_LIN tl
inner join inserted i
on tl.STR_ID = i.STR_ID
and tl.STA_ID= i.STA_ID
and tl.TKT_NO=i.TKT_NO

inner join ps_tkt_hdr th
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO

left outer join deleted d
on tl.STR_ID = d.STR_ID
and tl.STA_ID= d.STA_ID
and tl.TKT_NO=d.TKT_NO

Bill

-----Original Message-----
From:	Andrew Guattery [mailto:service@michaelsappliance.com] 
Sent:	Tuesday, December 21, 2004 11:08 AM
To:	Bill Meck
Subject:	trigger tribulations

Yet another goodie...
request for item ship date trigger:

A)	If the HDR SHIP_DAT is NULL, don’t modify the matching ITEM_SHIP_DAT date(s).
B)	If the HDR SHIP_DAT is not NULL, modify the ITEM_SHIP_DAT(s).
C)	If the HDR SHIP_DAT gets changed, and it DID MATCH(the original value before the change) the ITEM_SHIP_DAT(s), change the ITEM_SHIP_DAT(s) as well.
D)	If the HDR SHIP_DAT gets changed, and it DID NOT MATCH the ITEM_SHIP_DAT(s), leave the ITEM_SHIP_DAT(s) alone.


create trigger tr_Itemshipdat
on PS_TKT_HDR
for insert, update
as
set nocount on
update tl

set tl.ITEM_SHIP_DAT = th.SHIP_DAT
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID= tl.STA_ID
and i.TKT_NO=tl.TKT_NO
inner join ps_tkt_hdr th
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO

What do you think?

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-21 : 17:13:44

SO CLOSE...

CREATE TRIGGER tr_itemshipdate ON [dbo].[PS_TKT_HDR] 
FOR INSERT, UPDATE, DELETE 
AS
set nocount on
update tl set
tl.ITEM_SHIP_DAT = 
case 
when th.SHIP_DAT is null 
then tl.ITEM_SHIP_DAT
when th.SHIP_DAT is not null and tl.ITEM_SHIP_DAT is null 
then th.SHIP_DAT 
when th.SHIP_DAT is not null and d.SHIP_DAT = tl.ITEM_SHIP_DAT
then th.SHIP_DAT
when th.SHIP_DAT is not null and d.SHIP_DAT <> tl.ITEM_SHIP_DAT
then tl.ITEM_SHIP_DAT
end 
from PS_TKT_HDR th
inner join inserted i
on i.STR_ID = th.STR_ID
and i.STA_ID= th.STA_ID
and i.TKT_NO=th.TKT_NO

inner join ps_tkt_lin tl
on tl.STR_ID= th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO

left outer join deleted d
on d.STR_ID = th.STR_ID
and d.STA_ID= th.STA_ID
and d.TKT_NO=th.TKT_NO

where tl.str_id=th.str_id
and tl.sta_id=tl.sta_id
and tl.tkt_no=th.tkt_no
set nocount off

This all looks ok, but does not work. One thing I forgot is that the PS_TKT_LIN table has the sequence column SEQ_NO as part of its' key. could this have something to do with this not working? If I strip the trigger down to this:

update tl
set tl.ITEM_SHIP_DAT = th.SHIP_DAT
from inserted i
inner join PS_TKT_HDR th
on i.STR_ID = th.STR_ID
and i.STA_ID=th.STA_ID
and i.TKT_NO=th.TKT_NO

inner join PS_TKT_LIN tl
on tl.STR_ID = th.STR_ID
and tl.STA_ID=th.STA_ID
and tl.TKT_NO=th.TKT_NO

It returns the error 'cannot locate the row for updating'...
Any clues?
Bewildered Andy

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-22 : 16:47:18

Tara,
I found out why the original trigger wasn't working, and to fix it We need to move the trigger to the LIN table. Now I have an altogether different problem: On the PS_TKT_LIN table there is this trigger

create trigger tr_UpdateItemInvoiceWarranty
on dbo.PS_TKT_LIN
for insert, update
as

update tt
set tt.PROMPT_ALPHA_1 = th.PROF_ALPHA_1
from inserted i
inner join PS_TKT_LIN tt
on i.STR_ID = tt.STR_ID
and i.STA_ID = tt.STA_ID
and i.TKT_NO = tt.TKT_NO
and i.SEQ_NO = tt.SEQ_NO
inner join IM_ITEM th
on tt.ITEM_NO = th.ITEM_NO

update tt
set tt.PROMPT_ALPHA_2 = th.PROF_ALPHA_2
from inserted i
inner join PS_TKT_LIN tt
on i.STR_ID = tt.STR_ID
and i.STA_ID = tt.STA_ID
and i.TKT_NO = tt.TKT_NO
and i.SEQ_NO = tt.SEQ_NO
inner join IM_ITEM th
on tt.ITEM_NO = th.ITEM_NO

This trigger works fine. Now I've written this script:

create trigger tr_UpdateItemShipDate
on dbo.PS_TKT_LIN
for insert, update
as

update tl
set tl.ITEM_SHIP_DAT = 
case
when th.ship_dat is null
then tl.item_ship_dat
when th.ship_dat is not null and tl.Item_ship_dat is null
then th.ship_dat
when th.ship_dat is not null and tl.item_ship_dat=th.ship_dat
then th.ship_dat
when th.ship_dat is not null and tl.item_ship_dat<>th.ship_dat
then tl.item_ship_dat
end
from inserted i
inner join PS_TKT_LIN tl
on i.STR_ID = tl.STR_ID
and i.STA_ID = tl.STA_ID
and i.TKT_NO = tl.TKT_NO
and i.SEQ_NO = tl.SEQ_NO
inner join PS_TKT_HDR th
on tl.STR_ID = th.STR_ID
and tl.STA_ID = th.STA_ID
and tl.TKT_NO = th.TKT_NO

When I put this script on the table, and try to save a ticket, I get this error:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
I know what it means, but I'm not sure what to do about it. Any ideas?
Thanks again for the help!
Not-quite-a-people-hater-yet
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-26 : 16:12:54

Tara,
The problem all along has been the app. I put the two update triggers together (sorry, I just wasn't thinking), and they work properly when an INSERT is done outside the app. using the app, however, doesn't work. I am going to post this trigger problem in a new posting (so as not to confuse others with the original mess) and see if we can get it fixed.
Thanks so much for your help, hope you had a happy holiday!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources