I use a similar scheme for items (inventory control).
I use a SMALLINT whose value is 0 for a normal item and something > 0 for
a deleted item. My primary key on the item table is the item num itself
followed by the SMALLINT.

Works pleasantly until now.

Olivier Mascia,  om@...,  Senior Software Engineer
T.I.P. Group S.A.,  www.tipgroup.com,  Director


-----Original Message-----
From: Jörg Schiemann <schimmi@...>
To: "IB-Support" <ib-support@yahoogroups.com>
Date: Wed, 7 Feb 2001 12:48:12 +0100
Subject: [ib-support] Question about koncept

> Hi,
>
> maybe it's a silly question, but in that case I'll take it.
>
> I want to be able to undelete and to see who deletes the post.
> After deleting a customer the customer_no should be available again.
> Which koncept schould I use?
>
> I have a customer table
>
> CREATE TABLE CUSTOMER
> (
> CUSTOMER_NO NUMERIC(18,0) NOT NULL,
> NAME VARCHAR(50),
> CREATED_BY D_USERNAME,
> CREATE_DATE D_NOW,
> CHANGED_BY D_USERNAME,
> CHANGE_DATE D_NOW,
> CONSTRAINT PK_CUSTOMER_NO PRIMARY KEY (CUSTOMER_NO)
> );
> CREATE INDEX IDXCUSTOMER_NAME ON CUSTOMER (NAME);
>
> Now, I don't want that the user should be able to delete a customer,
> therefore I added a field DELETED.
>
> CREATE TABLE CUSTOMER
> (
> CUSTOMER_NO NUMERIC(18,0) NOT NULL,
> NAME VARCHAR(50),
> DELETED D_BOOLEAN,
> CREATED_BY D_USERNAME,
> CREATE_DATE D_NOW,
> CHANGED_BY D_USERNAME,
> CHANGE_DATE D_NOW,
> CONSTRAINT PK_CUSTOMER_NO PRIMARY KEY (CUSTOMER_NO)
> );
> CREATE INDEX IDXCUSTOMER_NAME ON CUSTOMER (NAME);
>
> If the user now 'deletes' a customer the field DELETED changes to true
> and
> the customer is no longer visible in the view.
> The only problem is that the customer_no the deleted customer had is
> no
> longer available and my customer, who I'm programming for, wants to be
> able
> to use the customer_no to a new customer.
> First I thouht about to create 2 new fields CUSTOMER_ID and
> CUSTOMER_NO_OLD
> like
>
> CREATE TABLE CUSTOMER
> (
> CUSTOMER_ID NUMERIC(18,0) NOT NULL,
> CUSTOMER_NO NUMERIC(18,0) NOT NULL,
> CUSTOMER_NO_OLD NUMERIC(18,0) DEFAULT 0,
> NAME VARCHAR(50),
> DELETED D_BOOLEAN,
> CREATED_BY D_USERNAME,
> CREATE_DATE D_NOW,
> CHANGED_BY D_USERNAME,
> CHANGE_DATE D_NOW,
> CONSTRAINT PK_CUSTOMER_ID PRIMARY KEY (CUSTOMER_ID),
> UNIQUE (KUNDEN_NR)
> );
> CREATE UNIQUE INDEX UIDX_CUSTOMER_NO ON CUSTOMER (CUSTOMER_NO);
> CREATE INDEX IDXCUSTOMER_NAME ON CUSTOMER (NAME);
>
> And foreign keys from other table points to CUSTOMER_ID
> If the user now deletes a customer DELTED becomes True, CUSTOMER_NO_OLD
> becomes CUSTOMER_NO and CUSTOMER_NO becomes a minus value.
> See the trigger I use.
>
> SET TERM !! ;
> CREATE TRIGGER TG_BU_AUDIT_DEL_CUSTOMER FOR CUSTOMER BEFORE UPDATE
> POSITION
> 99 AS
> BEGIN
> IF ( new.DELETED = 1 )
> THEN BEGIN
> new.CUSTOMER_NO_OLD = old.CUSTOMER_NO;
> new.CUSTOMER_NO = GEN_ID(GEN_CUSTOMER_NO_DEL, 1) * -1;
> End
> END !!
> SET TERM ; !!
>
> But do I actually need CUSTOMER_ID?
> The foreign keys in other tables do cascade update (ON UPDATE CASCADE).
>
> The advantage I see with CUSTOMER_ID is, that the database don't need
> to a
> cascade update to all involved tables.
>
> Q1. The Question is, how much work is that for the database to do a
> cascade
> update, is it worth it to skip it with CUSTOMER_ID ?
>
> Q2. Is there a better way?
>
> Q3. How do you handle deleting?
>
> Q4. Is that all nonsense?
>
> Thanks in advance for your reply.
>
> Best regards
>
> Jörg Schiemann
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~-~>
> eGroups is now Yahoo! Groups
> Click here for more details
> http://click.egroups.com/1/11231/0/_/_/_/981546427/
> ---------------------------------------------------------------------_-
> >
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>