Subject | Question about koncept |
---|---|
Author | Jörg Schiemann |
Post date | 2001-02-07T11:48:12Z |
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
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