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