Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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
0
datatechcorp
Asked:
datatechcorp
2 Solutions
 
PadawanDBACommented:
Does the following query pull the rows that you want moved to this debug/edit table that you want?

select
	*
from
	PS_DOC_LIN as PDL
		left join PS_DOC_LIN_PRICE as PDLP on PDL.DOC_ID = PDLP.DOC_ID and PDL.LIN_SEQ_NO = PDLP.LIN_SEQ_NO
where
	PDLP.DOC_ID is null and
	PDLP.LIN_SEQ_NO is null;

Open in new window


Edit: Also, could you please indicate which tables those columns you need are coming from?  I am assuming the are all coming from the PS_DOC_LIN table, but I am seeing some columns that indicate price, which would make me think they're in the PS_DOC_LIN_PRICE table...

Edit, Part 2: Further, if you want to enforce referential integrity, you would probably want to define a foreign key relationship.  This would have some other implications (ie. you wouldn't be able to insert the row into the PS_DOC_LIN table unless the value of the foreign key existed in the PS_DOC_LIN_PRICE table) that you would need to account for, however (ie. finding out why the corresponding PS_DOC_LIN_PRICE row is not there and fixing that).
0
 
Habib PourfardCommented:
By creating a trigger You can get noticed if a record is inserted to the PS_DOC_LIN table without any related record in price table.
as you probably insert a record to PS_DOC_LIN and then to price table with a delay, I added a delay of 1 second to this trigger. so it waits 1 second and then executes (it is not a good code but may be acceptable for a short period for test only).

by the way you can avoid this problem with doing your operation in a transaction. anyway here is the trigger:

CREATE TRIGGER CheckIfPriceRecordExits ON dbo.PS_DOC_LIN
    AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON;

		-- IT IS NOT A GOOD IDEA TO MAKE DELAY IN A TRIGGER (JUST FOR TEST)
		WAITFOR DELAY '00:00:01'

		DECLARE @DOC_ID BIGINT, @LIN_SEQ_NO INT
  
		SELECT @DOC_ID= DOC_ID , @LIN_SEQ_NO = LIN_SEQ_NO FROM inserted

		IF NOT EXISTS ( SELECT * FROM PS_DOC_LIN_PRICE WHERE DOC_ID = @DOC_ID AND LIN_SEQ_NO = @LIN_SEQ_NO)
		BEGIN
			INSERT  INTO EditTable (col1, col2, ..., colN)
            SELECT  col1, col2, ..., colN
			FROM PS_DOC_LIN 
			WHERE DOC_ID = @DOC_ID AND LIN_SEQ_NO = @LIN_SEQ_NO
		END
    END

Open in new window

0
 
datatechcorpAuthor Commented:
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
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Tackle projects and never again get stuck behind a technical roadblock.
Join Now