|
Subject:
|
capture error logs
|
|
Posted By:
|
jemacc
|
Post Date:
|
1/10/2007 10:23:27 AM
|
Hi am having some difficulty trying to capture sql errors from a trigger to a log table. Please see code below and make any recommendation
USE [shippingprod]
GO
/****** Object: Trigger [dbo].[tri_UPSShippingLog] Script Date: 01/10/2007 10:14:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tri_UPSShippingLog] ON [dbo].[UPSShippingLog]
FOR INSERT
AS
--TO DO:
-- create a public note indicating shipment took place, or do we want an activity
-- Make Decision, when we have a voided entry...
-- CARRIER_PRIO_CD = NULL -- I don't think there is a need fo this in the new design. Suppose the package isn't GROUND and its ovenight and the shipment fails, it would default back to ground.
declare @LineItemList as varchar(2000)
declare @VoidIndicator as Char(1)
declare @ShippingMethod as varchar(30)
declare @TrackingNumber as varchar(50)
declare @ShipDate as varchar(50)
declare @sSQL As Nvarchar(2000)
declare @UPSShippingLogID as int
declare @err as int
declare @RowsAffected as int
select @LineItemList = LineItemList,
@VoidIndicator =INSERTED.VoidIndicator,
@ShippingMethod =INSERTED.ShippingMethod,
@TrackingNumber = INSERTED.TrackingNumber,
@ShipDate = INSERTED.ShipDate,
@UPSShippingLogID = INSERTED.ID
FROM INSERTED
if dbo.uf_IsValidList(@LineItemList) =0
BEGIN
set @sSQL = 'The Line Item List is invalid'
GOTO PROBLEM
END
IF @VoidIndicator = 'Y'
BEGIN
-- Remove the tracking number and other info
set @sSQL =
'UPDATE siebprod.dbo.S_ORDER_ITEM ' +
'SET X_TRACKING_NUM = NULL, '+ -- Tracking Number
' MUST_DLVR_BY_DT = NULL, ' + -- Ship Date
' DLVRY_STATUS_CD = ''Voided'''+ -- Shipping Status, 'Voided' -- 'Invalid address' is a valid choice from select * from S_LST_OF_VAL WHERE TYPE = 'WIN_SHIPPING_STATUS';
' WHERE ROW_ID IN (' +@LineItemList + ')'
END
Else
BEGIN
If @LineItemList is Null
set @LineItemList = 'NULL'
If not @ShippingMethod is NULL
set @ShippingMethod = char(39) + @ShippingMethod + char(39)
else
set @ShippingMethod = 'NULL'
If @TrackingNumber is not NULL
set @TrackingNumber = char(39) + @TrackingNumber + char(39)
else
set @TrackingNumber = 'NULL'
IF @ShipDate is not NULL
set @ShipDate = char(39) + @ShipDate + char(39)
else
set @ShipDate = 'NULL'
-- Update the LineItems table with the data retrieved from UPS WorldShip.
set @sSQL =
'UPDATE siebprod.dbo.S_ORDER_ITEM' +
' SET X_TRACKING_NUM = ' + @TrackingNumber + ',' +
' MUST_DLVR_BY_DT = CONVERT(datetime, '+@ShipDate+'),' +
' DLVRY_STATUS_CD = ' + Char(39) + 'Shipped' + Char(39) + ',' +
' CARRIER_PRIO_CD = ' + @ShippingMethod +
' WHERE ROW_ID IN ('+@LineItemList+') '
END
exec @err = sp_executesql @sSQL
set @err = @@ERROR
IF (@err <> 0) GOTO PROBLEM
set @err = @@error
set @RowsAffected = @@rowCount
If @err <> 0
INSERT INTO UPSShippingLogTriggerErrors(UPSShippingLogID, ErrorNumber, SQLStatement)
VALUES(@UPSShippingLogID, @err, @sSQL)
Else
-- Only continue to try to close the line item if the first update was successful.
BEGIN
-- this statement will log the resulting action of the trigger
INSERT INTO UPSShippingLogTriggerSQL(UPSShippingLogID, SQLStatement, RowsAffected)
VALUES (@UPSShippingLogID, @sSQL, @RowsAffected)
-- we will need to re-open the line items when the recognized amount has not yet been set
-- This can't be accomplished in the SQL statement above because we will still need to perform updates
-- to the tracking number field on the line item even if the status can't be re-opened.
-- When updating a line item with a tracking number set the STATUS_CD to 'Closed'
-- When updating a line item without a tracking number set the STATUS_CD to 'New'
-- Do not allow any trigger updates on the STATUS_CD from 'Closed' to 'New'
-- for line items that has a recognized amount that has been returned from solomon X_REGOGNIZED_AMT
IF @VoidIndicator = 'Y'
-- re-open the line item
set @sSQL =
'UPDATE siebprod.dbo.S_ORDER_ITEM ' +
'SET STATUS_CD = ''New'' '+
' WHERE ROW_ID IN (' +@LineItemList + ')' +
' AND isnull(X_RECOGNIZED_AMT,0) = 0 '
Else
-- close the line item
set @sSQL =
'UPDATE siebprod.dbo.S_ORDER_ITEM ' +
' SET STATUS_CD = ''Closed'' '+
' WHERE ROW_ID IN (' +@LineItemList + ')' +
' AND isnull(X_RECOGNIZED_AMT,0) = 0 '
exec @err = sp_executesql @sSQL
set @err = @@error
set @RowsAffected = @@rowCount
If @err <> 0
GOTO PROBLEM
Else
INSERT INTO UPSShippingLogTriggerSQL(UPSShippingLogID, SQLStatement, RowsAffected)
VALUES (@UPSShippingLogID, @sSQL, @RowsAffected)
END
RETURN
PROBLEM:
INSERT INTO UPSShippingLogTriggerErrors(UPSShippingLogID, ErrorNumber, SQLStatement)
VALUES(@UPSShippingLogID, @err, @sSQL)
Jaime E. Maccou
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
1/10/2007 7:17:22 PM
|
The fundamental problem with your trigger is that it will only handle one row of change even if a thousand have changed. You need to convert this to set based programming.
--Jeff Moden
|
|
