Link to home
Create AccountLog in
Avatar of datatechcorp
datatechcorpFlag for United States of America

asked on

MSSQL Edit Script Needed

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
SOLUTION
Avatar of PadawanDBA
PadawanDBA

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of datatechcorp

ASKER

Thank you both for chiming in here...this is most helpful!  We'll try the trigger approach.  We cannot alter the schema of the tables, because it's commercial software, and it can be overwritten, at any time, by the author...so we're hesitant to do anything from a FOREIGN KEY standpoint.  But, this will point us in the right direction.  Again...THANK YOU BOTH!...Mark