March 24, 2005 at 9:15 am
Hello group!
I am having problem with web requisition application. User placed order and after shipment arrived will have to complete order with qty received. At this point he may see only order header and no order details. Some how details got deleted. This happened for different users and I know about invalid order only after user has to receive it. There is no way for user to delete it after it was submitted to supplier.
My solution at this point was to create trigger & log table which I did. I am wondering if you can suggest adding to my trigger more that potentially can help me to resolve this puzzle. Something like process name (sproc name , etc..)
Here is my trigger.Thanks a lot . Mark Gorelik
create TRIGGER dlvry_det_audit
ON delivery_detail
FOR Delete
AS
BEGIN
Insert dlvry_det_log
select i.thtr_num,
i.delivery_id,
h.webreq_order_num,
count(*)as lines,
h.status,
'D',
getdate(),
(select substring(system_user,charindex('\',system_user)+1,40)),
Host_NAME()
from deleted i left join delivery_header h
on i.thtr_num=h.thtr_num and i.delivery_id=h.delivery_id
group by i.thtr_num ,i.delivery_id,h.status,h.webreq_order_num
END
March 25, 2005 at 6:21 am
Are you sure the data is being deleted, or is there an error in the code and the details never get saved in the first place?
you could grab some of the log tracing tools out there and find out for sure, or start running profiler on the database and capture all the changes until you know where the error is.
I think it is more likely that the data was never inserted, and maybe the error was suppressed, than that the data was inserted and then some other process deleted it.
Lowell
March 28, 2005 at 10:37 am
Hello Lowell ! Thanks for looking into my problem. I would think so too that insert never happened on delivery details table. Still have few cases when actually theatre received order (delivery header and delivery details table were present) and delivery details has been deleted after the fact. I always have delivery header information in the table.
Here is sproc responsible for this insert. Maybe group can take a look and give me suggestions on how stop this inconsistency. Thanks . Mark
CREATE PROCEDURE [sp_move_order_to_ics]
@thtr_num smallint,
@order_num int
AS
DECLARE @next_id int
DECLARE @supplier_id int
DECLARE @user_id char(12)
DECLARE @last_id int
set @next_id = (select isNull(max(delivery_id),0) + 1 as newid FROM delivery_header WHERE thtr_num = @thtr_num)
SET @supplier_id = (select supplier_id FROM conc_order_header H WHERE H.user_id = right('0000' + rtrim(convert(char(12), @thtr_num)),4) AND H.order_num = @order_num)
SET @user_id = right('0000' + rtrim(convert(char(12), @thtr_num)),4)
If (select count(*) from delivery_header WHERE webreq_user_id = @user_id and webreq_order_num = @order_num) = 0
Begin
--insert header
INSERT INTO delivery_header (thtr_num, delivery_id, status, ordered_by, order_date, supplier_id, webreq_user_id, webreq_order_num, webreq_append_id, append_flg)
SELECT @thtr_num, @next_id, 'PN', ordered_by, order_date, @supplier_id, @user_id, @order_num, 'A', 0
FROM conc_order_header WHERE order_num = @order_num AND user_id = @user_id
--insert details
INSERT INTO delivery_detail (thtr_num, delivery_id, line_num, supplier_code, inv_code, description,
supplier_conversion, supplier_unit, counting_unit, standard_unit_cost, order_qty, rbo_group)
SELECT @thtr_num, @next_id, part_id, isNull(supplier_code,d.Line_code), isnull(I.inv_code,'N/A'), D.description, isNull(I.delivery_conversion,1), isNull(I.delivery_unit,d.unit), isNull(C.counting_unit,'N/A'),
isNull(S.standard_cost,isNull(jur_price,0)), D.quantity, isNull(C.rbo_group, isNull(non_inventory_rbo_group, 3))
FROM conc_order_header H
INNER JOIN conc_order_detail D ON H.user_id = D.user_id AND H.order_num = D.order_num
LEFT JOIN vw_supplier_theatre_list I ON I.thtr_num = @thtr_num AND I.supplier_id = @supplier_id AND D.part_id = I.ref_id
LEFT JOIN inventory_codes C ON C.inv_code = I.inv_code
LEFT JOIN inventory_standard_cost S ON S.inv_code = C.inv_code
AND (S.start_date <= GETDATE ())
AND (S.end_date IS NULL OR S.end_date >= GETDATE ())
WHERE H.user_id = @user_id AND H.order_num = @order_num
March 28, 2005 at 1:02 pm
offhand, i'd bet a buck on the second sql statement, which inserts into delivery_detail.
i'd say change the 3 LEFT JOIN's to LEFT OUTER JOIN's
if the join for those 5 tables encounter just one null or missed join(possible because you are join id's on concatenated strings), the select statement will return no rows, and the proc will not fail with an error, it simply find any rows to insert.
you could test it like this:
after the last line of the proc:
WHERE H.user_id = @user_id AND H.order_num = @order_num
if @@rowcount =0
RAISERROR ('No Row Detail Records which matched the complex join statements criteria', 16, 1)
other minor issues i saw were:
newid is a reserved word and is used as a column name; minor but bad practice.
@thtr_num is defined as a smallint, which has a max value of 32767. your code seems to be grabbing the rightmost 4 characters of that value as a varchar; you will run out of user_ids with the 10000 record and start duplicating.
ie:
declare @thtr_num smallint
set @thtr_num=2012
select right('0000' + rtrim(convert(char(12), @thtr_num)),4)
set @thtr_num=12012
select right('0000' + rtrim(convert(char(12), @thtr_num)),4)
set @thtr_num=22012
select right('0000' + rtrim(convert(char(12), @thtr_num)),4)
set @thtr_num=32012
select right('0000' + rtrim(convert(char(12), @thtr_num)),4)
all return 2012
Lowell
March 28, 2005 at 1:18 pm
Thanks again!
I will follow your recommendations and will give it to try. What do You think about adding some logic after insert completed that will check transaction in both tables?
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply