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
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
Microsoft SQL Server 2008Microsoft SQL Server
Last Comment
datatechcorp
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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