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