troubleshooting Question

MSSQL Edit Script Needed

Avatar of datatechcorp
datatechcorpFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
3 Comments2 Solutions356 ViewsLast Modified:
Hello All...

We're encountering errors in our Invoicing programs, that we need an EDIT to help us error trap & debug this problem with.

Currently, we have two related tables in MSSQL, namely:

PS_DOC_LIN
   Primary Key:
      [DOC_ID] [dbo].[T_DOC_ID] NOT NULL,
      [LIN_SEQ_NO] [dbo].[T_SEQ_NO] NOT NULL

PS_DOC_LIN_PRICE
   Primary Key:
      [DOC_ID] [dbo].[T_DOC_ID] NOT NULL,
      [LIN_SEQ_NO] [dbo].[T_SEQ_NO] NOT NULL,
      [PRC_SEQ_NO] [dbo].[T_SMALLINT] NOT NULL

The DOC_NO and LIN_SEQ_NO for each record have to match...that's just the way the tables were designed.

What we're finding, is that in certain circumstances (very intermittent...hence, the need for this debug/edit list), there is *NO* corresponding record in the PS_DOC_LIN_PRICE table...and there *MUST* be one.  So, what we need, and we've been banging our heads against the wall trying to find the right syntax to do this...is a script that simply creates a record in some EDIT table...where there is *NO* corresponding record...in the PS_DOC_LIN_PRICE table...any time a record is added to the PS_DOC_LIN table.  Simple, simple.  Then, we'll be in a better position, to help isolate these occurences.

In that "EDIT" table, we'll need the following fields:

DOC_ID (bigint)
LIN_SEQ_NO (int)
STR_ID (varchar(10))
STA_ID (varchar(10))
TKT_NO (varchar(15))
LIN_TYP (varchar(1))
PRC (money)
ITEM_NO (varchar(20))
CALC_PRC (money)
DESCR (varchar(50))
QTY_SOLD (decimal(15,4))
EXT_PRC (decimal(15,2))
PRC_1 (money)
ORIG_QTY (decimal(15,4))
QTY_SHIPPED (decimal(15,4))

We're not certain...if this needs to be a Trigger...or a Stored Proc (presumably a trigger?), but we need to have it pretty quickly...as our Invoicing software...is shipping out items...with a ZERO price...not good!

PLEASE HELP...we'd very much appreciate it!...Thanks!...Mark
ASKER CERTIFIED SOLUTION
Habib Pourfard
Senior Software Engineer
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros