View Single Post
  #1 (permalink)  
Old January 10th, 2007, 11:23 AM
jemacc jemacc is offline
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default capture error logs

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
__________________
Jaime E. Maccou
Reply With Quote