Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 10th, 2007, 11:23 AM
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
  #2 (permalink)  
Old January 10th, 2007, 08:17 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to capture java syntax error messages? help.. viber101 Java Databases 2 April 1st, 2009 12:53 AM
mod_jk2 error in apache logs psimoneschi Apache Tomcat 0 September 12th, 2007 12:11 PM
Capture Sql server Error happygv SQL Server 2000 19 May 10th, 2007 12:55 AM
Transaction Logs kilika SQL Server 2000 17 September 30th, 2004 09:26 AM
Error capture from DLL c3r3al_kill3r Classic ASP Components 0 July 1st, 2003 05:55 AM



All times are GMT -4. The time now is 12:50 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.