Solved

MSSQL Edit Script Needed

Posted on 2014-01-06
3
316 Views
Last Modified: 2014-01-06
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
Comment
Question by:datatechcorp
3 Comments
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 250 total points
Comment Utility
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
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:datatechcorp
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now