----- Original Message -----

From: Jörg Schiemann

To: ib-support@yahoogroups.com

Sent: Wednesday, February 07, 2001 4:14 PM

Subject: Re: [ib-support] Question about koncept

Hi,

that means a lot of moving.
Is there an easy SQL command for moving a master + details?
--
Regards,
Jörg Schiemann
----- Original Message -----
From: "Ali Gokcen" <alig@...>
To: <ib-support@yahoogroups.com>
Sent: Wednesday, February 07, 2001 1:18 PM
Subject: Re: [ib-support] Question about koncept


> Hi Jörg,
> I think  archival master and detail tables may be easy and clean.
> move your master(+details)  to an archive master (+detail) table,  in
before
> delete trigger of your master table,
> and delete all details.
> is there an SQL error  IB wont  delete the master record because
referantial
> integrity and you can rollback all.
> of course, index of archive tables cant be UNIQUE.
> you can find a deleted  ID number easly with compare master and
> archive_master tables by a SQL query.
>
> Regards,
> Ali
> ----- Original Message -----
> From: Jörg Schiemann
> To: IB-Support
> Sent: Wednesday, February 07, 2001 1:48 PM
> 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
>
> www.
>
>
>
>
> 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
>
>
>
>



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com