HELP! I can't get this trigger to work!

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)

HELP! I can't get this trigger to work!

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-04-17 : 18:49:33

Once again I bow my humble heads to the guru's here at SQLTEAM.com...
I have two tables, PS_TKT_HDR and PS_TKT_NOTES. In PS_TKT_HDR there is a note column that is text. the app we work with won't display text blobs in certain views, but it will display varchar columns. I created the PS_TKT_NOTES table using the same key columns (first three in the code below) and the text column. heres the trouble: The only way this trigger will work is if I manually insert data into the PS_TKT_NOTES table that match in the primary key columns, and then it just keeps overwriting the same rowset! I know I have the trigger written wrong, but I have no idea how to fix it!

create trigger tr_Updateticketnotes
on ps_tkt_hdr
for insert, update
as
update tt
set tt.str_id = th.str_id
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_notes tt
on th.str_id = tt.str_id
and th.sta_id = tt.sta_id
and th.tkt_no = tt.tkt_no

update tt
set tt.sta_id = th.sta_id
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_notes tt
on th.str_id = tt.str_id
and th.sta_id = tt.sta_id
and th.tkt_no = tt.tkt_no

update tt
set tt.tkt_no = th.tkt_no
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_notes tt
on th.str_id = tt.str_id
and th.sta_id = tt.sta_id
and th.tkt_no = tt.tkt_no

update tt
set tt.cust_no = th.cust_no
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_notes tt
on th.str_id = tt.str_id
and th.sta_id = tt.sta_id
and th.tkt_no = tt.tkt_no

update tt
set tt.tkt_dat = th.tkt_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_notes tt
on tt.str_id = th.str_id
and tt.sta_id = th.sta_id
and tt.tkt_no = th.tkt_no

update tt
set tt.tkt_notes = th.svc_notes
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_notes tt
on tt.str_id = th.str_id
and tt.sta_id = th.sta_id
and tt.tkt_no = th.tkt_no

HELP!!

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-18 : 06:22:48

If you use this, you shouldn't need to do your manula cut/paste either.

CREATE TRIGGER tr_Updateticketnotes
ONps_tkt_hdr
FOR INSERT, UPDATE
AS

UPDATE tt
SET
tt.str_id = th.str_id,
tt.sta_id = th.sta_id,
tt.tkt_no = th.tkt_no,
tt.cust_no = th.cust_no,
tt.tkt_dat = th.tkt_dat,
tt.tkt_notes = th.svc_notes
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_notes tt ON th.str_id = tt.str_id
AND th.sta_id = tt.sta_id
AND th.tkt_no = tt.tkt_no

INSERT tt(
str_id,
sta_id,
tkt_no,
cust_no,
tkt_dat,
tkt_notes)

SELECT
th.str_id,
th.sta_id,
th.tkt_no,
th.cust_no,
th.tkt_dat,
th.svc_notes
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
LEFT OUTER JOIN ps_tkt_notes tt ON th.str_id = tt.str_id
AND th.sta_id = tt.sta_id
AND th.tkt_no = tt.tkt_no
WHERE
tt.str_id IS NULL
AND tt.sta_id IS NULL
AND tt.tkt_no IS NULL

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-04-18 : 20:00:21

Derrick!
Once again you pop up to show me the way! I copied the trigger, and the syntax checks ok in SQL server, but when I try to save a ticket using the app is errors out with "invalid object name tt"...
What am I missing?
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-04-18 : 20:32:46

New update to this trigger issue:
I changed INSERT tt(
to INSERT ps_tkt_notes (
This fixed the 'unknown object' problem. But there is now one other problem. If I create a ticket, and the trigger is as it stands, it blows up. If I cut the UPDATE section out and paste it after the INSERT section, then the ticket goes in ok. If I edit a ticket (one that is already in the two tables) it will blow up if I don't switch the UPDATE section back to the top of the trigger! I'm really getting the hang of sprocs and datatypes and the such, but this is way nutzo!

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-04-18 : 20:59:06

I got it!
I added

if exists (select tt.str_id, tt.sta_id,tt.tkt_no
from ps_tkt_notes tt, inserted i where tt.str_id = i.str_id and tt.sta_id = i.sta_id
and tt.tkt_no = i.tkt_no)

to the top of the trigger before the UPDATE section, and bam! it works! Now I have another little quandry: when the ticket is an oder, it puts one entry into the ps_tkt_notes table. when the order gets released to a ticket (the tkt_no for orders and tickets are different) it sticks ANOTHER entry into the ps_tkt_notes table. I could fix this by putting a DELETE trigger on the ticket history table so that when the tickets are posted at the end of day it deletes the order rows, but is there a better way?

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-18 : 21:37:52

I'm getting a little confused reading the email. You might want to post what you have for the trigger again. I think you can actually solve your problem by having one trigger for UPDATE and one for INSERT. Your code would also be cleaner that way.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-04-19 : 06:38:41

ok Derrick,
Here you go...

CREATE TRIGGER tr_Updateticketnotes
ON ps_tkt_hdr
FOR INSERT, UPDATE
AS

if exists (select tt.str_id, tt.sta_id,tt.tkt_no
from ps_tkt_notes tt, inserted i where tt.str_id = i.str_id and tt.sta_id = i.sta_id
and tt.tkt_no = i.tkt_no)

UPDATE tt
SET 
tt.str_id = th.str_id,
tt.sta_id = th.sta_id,
tt.tkt_no = th.tkt_no,
tt.cust_no = th.cust_no,
tt.tkt_dat = th.tkt_dat,
tt.svc_notes = th.svc_notes
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_notes tt ON th.str_id = tt.str_id
AND th.sta_id = tt.sta_id
AND th.tkt_no = tt.tkt_no

INSERT ps_tkt_notes(
str_id,
sta_id,
tkt_no,
cust_no,
tkt_dat,
svc_notes)

SELECT
th.str_id,
th.sta_id,
th.tkt_no,
th.cust_no,
th.tkt_dat,
th.svc_notes
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
LEFT OUTER JOIN ps_tkt_notes tt ON th.str_id = tt.str_id
AND th.sta_id = tt.sta_id
AND th.tkt_no = tt.tkt_no
WHERE
tt.str_id IS NULL
AND tt.sta_id IS NULL
AND tt.tkt_no IS NULL

I think the problem I was having is with the way the app is written, which we have no control over. The section in red is what I added that smoothed things out. Basically, this whole operation is a workaround for notes in the app: text fields on tickets can be printed, but they just appear as (MEMO) in the app due to their unlimited size capability. Since we use the note field for notes that are short (a few hundred characters) we can display them in the app. In order to fool the app, we need to stick the note into a varchar column rather than a text column, and hence this whole trigger/table setup. When a customer places an order, the PS_TKT_HDR table gets a rowset inserted with a 5 digit TKT_NO starting with a 7. When the Order arrives and the customer picks it up, the order is released; a new rowset is entered into PS_TKT_HDR with a TKT_NO starting with a 1, and the order rowset is deleted. THere is a column in the HDR table called DOC_TYP that is populated with (O)rder,(T)icket,(Q)uote, ect. and I was thinking maybe we could use that to delete the PS_TKT_NOTES rowset from the order when it is released. The PS_TKT_HDR tables gets copied to PS_TKT_HIST at the end of day, and all (T)ickets get deleted from PS_TKT_HDR. All other DOC types stay. Does this help?
Andy

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-20 : 07:56:18

I wish I had some time to look at this today. lol You just gave me a headache. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-04-20 : 13:08:46

I know! It's enough to make a grown man give up beer! (well, maybe not..)

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