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
datatechcorpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
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).
Habib PourfardSoftware DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.