App update trigger issue - 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)

App update trigger issue

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-23 : 17:00:07

Oh, It never ends here in Andyland:
Look at the following trigger. The idea here is that when a ticket gets deleted from PS_TKT_HDR, the corresponding info in Timetable gets deleted, also. Problem is, The trigger is deleting the info from the timetable even when we do an update to the ticket in PS_TKT_HDR, such as take a deposit. It could be possible that when we do something like take a deposit, the row in PS_TKT_HDR is DELETED and then INSERTED, rather than just UPDATED:

CREATE TRIGGER updatetimetable ON [dbo].[PS_TKT_HDR] 
FOR  DELETE 
AS
update t1
set TIME01=null,
City01=null,
CUST01=null
from Timetable t1
inner join deleted d on d.tkt_no=t1.time01


update t1
set TIME04=null,
City04=null,
CUST04=null
from Timetable t1
inner join deleted d on d.tkt_no=t1.time04

update t1
set TIME07=null,
City07=null,
CUST07=null
from Timetable t1
inner join deleted d on d.tkt_no=t1.time07

update t1
set TIME10=null,
City10=null,
CUST10=null
from Timetable t1
inner join deleted d on d.tkt_no=t1.time10

update t1
set TIME13=null,
City13=null,
CUST13=null
from Timetable t1
inner join deleted d on d.tkt_no=t1.time13

update t1
set TIME16=null,
City16=null,
CUST16=null
from Timetable t1
inner join deleted d on d.tkt_no=t1.time16

This timetable is a real handfull, and is denormalized in order to get proper info to the app. We've ironed out all of the kinks up to this point, and this may finally be the last one. Any ideas? This trigger looks ok to me, but what do I know...
I tried to capture this in Profiler, but had no luck in proving out whether or not the app is doing a DELETE/INSERT or just an UPDATE.
Assuming it is DELETE/INSERT, how can we get this trigger to work? Or do I just have something stupid wrong with the syntax?(That never happens)
Andy

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

Kristen
Test

22859 Posts

Posted - 2005-04-24 : 03:00:49

Change the trigger to DELETE, UPDATE and add a NOT EXISTS on the INSERTED pseudo-table?

[EDIT] Agghhh! Are you meaning that the APP might do a DELETE an then an INSERT instead of an UPDATE?

If so you'll have to handle the INSERT in a TRIGGER. Would it do any harm to do an Update, like the one in your DELETE TRIGGER for items which are INSERTED? Presumably it only needs to bother when the TIMEnn column is NULL (i.e. from some previous DELETE)

Kristen

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-25 : 07:52:39

Isn't this enough to make you nuts? I THINK what is happening is this:
When a user types an order, a row is inserted into PS_TKT_HDR, pretty cut and dry. The problem comes in when a user EDITS this row, for instance taking a deposit on the order. When this happens, the app creates a 2nd NEW row for the order, this time as an invoice for the deposit payment. It looks like the app is taking the original row, locking it while the user edits it, then DELETES IT and inserts TWO new rows, one for the deposit and one to replace the first one. Even as I type this it sounds too wierd to be right

, but that sure is what it looks like it is doing! I'm looking for some help from the vendor, but so far no luck.

AAAAAGGGGHHHH!!
>BUT!<
Assuming the app is doing just an update, why would this trigger fire when a user takes a deposit on an existing row of data? It would simply update the row, and this trigger should not fire. The Timexx columns are populated with data from PS_TKT_HDR.TKT_NO info, and then the Cityxx and Custxx columns are populated with a trigger on dbo.Timetable. Custxx and Cityxx are the denormalized part of this mess, but it gets worse:
Timetable is filled with rows of data, PK on cols SVC_DATE and SERVICER. This table is filled off a proc and a udf, and then everything that is done here is UPDATE only, as we never remove the date and servicer history. Can you say D-E-N-O-R-M-A-L-I-Z-A-T-I-O-N?
So...
I will play withthis a bit, but any and all ideas would be GREATLY appreciated!
andy

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-25 : 08:04:07

Run Profiler on the code/app to prove your theory that..."It looks like the app is taking the original row, locking it while the user edits it, then DELETES IT and inserts TWO new rows, one for the deposit and one to replace the first one". This may give you a stick with which to beat the vendor.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-25 : 14:06:34

Hey Andrew,
Yeah, I need to go back to profiler and run it again. I tried it once, and there was just too much traffic at that time to really see it. I will run it here on my laptop later today and see what's up. I have a feeling I'll be posting a want ad for volunteers to join the party and go flog a venduh or two!
I will post the results later tonight...
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 - 2005-04-26 : 21:46:28

Well,
The results are in, and they aren't pretty. As I thought, editing an order calls a proc named USP_DEL_PS_ORD, which captures the row data and then deletes it

from PS_TKT_HDR. The app then turns around and does an INSERT using the captured data

. Lord only knowswhy they would delete a row and then reinsert the exact same data, but that is the deal.
SO,
Here is the dilemma, kiddies. When an order gets PERMANENTLY deleted from PS_TKT_HDR, I need to remove the matching order number from the Timexx columns in TIMETABLE, but not touch anything when this silly-ass proc fires. The trigger firing AFTER the proc would be fine, but since FOR and AFTER are the same, I am stumped.
Anyone have any idea about how to work around this crappy design feature that I have?
Andy

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

Kristen
Test

22859 Posts

Posted - 2005-04-27 : 13:25:02

Well, the only (rather crappy) thought I have is to use a DELETE trigger to do as-if its a real Delete, but also store the data in some Virtual Ether, and then the INSERT trigger to check the Virtual Ether and reinstate it how it was if it turns out to be a Delete+Insert maneouver, otherwise do nothing (i.e. a New Insert).

Bummer!

Kristen

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-28 : 20:46:28

Hey Kristen,
Your idea may not beas bad asit seems. Suppose we make another table, say ACTIV_TKTS, with a column that holds ticket numbers. Now we put a trigger on PS_TKT_HDR that updates ACTIV_TKTS on every INSERT or UPDATE, but not DELETE. NOW we put a trigger on ACTIV_TKTS that fires on every UPDATE that checks PS_TKT_HDR for tickets that exist in ACTIV_TKTS but NOT in PS_TKT_HDR, and then DELETES from ACTIV_TKTS AND UPDATES Timetable accordingly. What do you think?
Andy

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

Kristen
Test

22859 Posts

Posted - 2005-04-30 : 10:04:41

Sounds OK to me.

I'd also have a date/time on PS_TKT_HDR so that real deletes got purged at some future point - presumable you could delete anything more than 24 hours old?

What happens if there is a regular DELETE, followed by a record, with the same original PK, being inserted - any danger it would get the "saved" data from ACTIV_TKTS?

Kristen

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-30 : 13:13:30

It gets even more interesting!
I've created a this table:

create table ACTIV_SVC_TKTS
(
STR_ID varchar(10), STA_ID varchar(10),TKT_NO varchar(15)
CONSTRAINT
	PK_ACTIV_SVC_TKTS PRIMARY KEY CLUSTERED 
	(
	STR_ID,STA_ID,
TKT_NO)
SVC_DATE datetime,
SVC_TIME varchar(15)
)

The key for this table is identical to PS_TKT_HDR, the table we are trying to get the tickets from.
I've been fighting withthe trigger to copy tickets from PS_TKT_HDR to ACTIV_SVC_TKTS for about an hour now, and am getting nowhere. I can get the trigger to INSERT a new record, but the minute we take a deposit (which is when the order gets deleted and re-inserted), I get "cannot locate row for update" errors. The current trigger looks like this:

create trigger activetickets on dbo.PS_TKT_HDR
for insert,update
as 
declare @tkt varchar(15)
set @tkt=(select tkt_no from inserted)
if exists(select tkt_no from activ_svc_tkts where tkt_no=@tkt)
begin
update activ_svc_tkts
set str_id=inserted.str_id,sta_id=inserted.sta_id,
tkt_no=inserted.tkt_no,svc_date=inserted.svc_date,
svc_time=inserted.svc_time
from inserted
where inserted.tkt_no=activ_svc_tkts.tkt_no
and inserted.str_id=activ_svc_tkts.sts_id
and inserted.sta_id=activ_svc_tkts.sta_id
and inserted.doc_typ='O' and 
(inserted.is_svc_call='Y' or inserted.is_svc_call='C')
end
else
insert into activ_svc_tkts
(str_id,sta_id,tkt_no,svc_date,svc_time)
select str_id,sta_id,tkt_no,svc_date,svc_time
from inserted
where inserted.doc_typ='O' and 
(inserted.is_svc_call='Y' or inserted.is_svc_call='C')

There is not aliasing at this point as I am trying to figure out why this keeps blowing up!
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