Hi Olivier,

that's a very good idea, but I think you don't have any detail table(s).

Best regards,
Jörg Schiemann

----- Original Message -----
From: "Olivier Mascia" <om@...>
To: <ib-support@yahoogroups.com>
Sent: Wednesday, February 07, 2001 4:29 PM
Subject: Re: [ib-support] Question about koncept


> 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
> >
> >
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
>