RE: SQL-Ledger-users digest, Vol 1 #113 - 15 msgs

[csaunders <csa...@da...>]

You could write a trigger  (i assume postgres supports triggers?)

usually modern sql engines support theoretical temp tables (in ms sql they 
are #inserted and #deleted i think) that allow you to check data as it is 
added or deleted. you could check the id against existing invoices (same 
for dup customer info as well) and ignore if null or blank and roll back 
with message if duplicate found. don't know how the front end will cope 
with a roll back.

in very rough psuedo code:
*********************************
create trigger blah

@invoice_id integer
@invoice_id = select invoice_id from #inserted

if @invoice_id in (select invoice_id from invoice_tbl) ----hope there is a 
good index on here!
then comit
else roll back

end trigger
*********************************

maybee this could be generised with some front end form for the most 
important tables, invoice, customer, supplier (not sure of what the SL erd 
is though...)

cs
===============================
Message: 1
Date: Fri, 28 Dec 2001 13:40:33 -0700 (MST)
From: Dieter Simader <dsi...@sq...>
To: Roger Samour <rs...@ma...>
cc: sql-ledger users mailing list <sql...@li...>
Subject: Re: Duplicity?

Invoices are distinguished by an internal ID, not the invoice number or
a combination of invoice number and name.

So, yes, you can enter the same information again, however, the invoices
are different.


Dieter Simader    http://www.sql-ledger.org   (780) 472-8161
DWS Systems Inc.     Accounting Software       Fax: 478-5281
=========== On a clear disk you can seek forever ===========

On Fri, 28 Dec 2001, Roger Samour wrote:

> Hi, I was entering a Vendor invoice, and just to test it I entered
> another invoice with the same Invoice number and under the same vendor
> and it allowed me to do that. Am I doing something wrong? or does it
> allow duplicate invoice numbers under the same vendor?
>
> Roger J. Samour
>
>
>



--__--__--

Message: 2
From: alta <al...@al...>
Reply-To: al...@al...
Organization: Alta Research
To: <sql...@li...>
Subject: Re: Duplicity?
Date: Fri, 28 Dec 2001 13:08:55 -0800


Roger...

If you are concerned that the possibility of creating two invoices
with the same number is an integrity issue (or a potential for
confusion), consider trying a UNIQUE constraint on the invoice number
field.

However, I tried something similar to prevent duplicate customer
records, and got an error.  If I understand Dieter's explanation,
this is because the record is created with a null in the id field.
So, my sggestion may not work for other tables, either.

... Reed