Disable record deletion

[Scott]

I would like to set up the table that disable the record deletion. Once the
record is added, contents of the records are allowed but the records cannot
be deleted. Is it mission possible and how to do if so?

Thanks,

Scot

 

[Guest]

Scott,

It is not good practice to disable the table from anything. You have to use
a form eg in datasheet view where you can set the different properties, One
of the options is allow deletions which you can set to no. Don't present the
end users with the tables or databasewindow.

hth

 

[Guest]

Agree with Maurice.
Suggest you use a "Deleted/Disabled" field (boolean ((Yes/No)) in your table.
Have a query that Selects only records with this field set to No (False) as
the norm for your Form.
A User could then be able to update this Field on the form (if you let
them), to "delete" the record.

Just a wee thought - hope it helps.

Cheers
Auld Mannie

 

[Jamie Collins]

Suggest you use a "Deleted/Disabled" field (boolean ((Yes/No)) in your table.

Have you tried this for real? It is a pain to maintain because you
(and everyone else who uses the data) has to include

AND MyTable.MyDeletedField = 'N'

in every join condition and search condition in every query and SQL
statement that uses the table, bar the *exceptional* circumstances
where you want to see 'deleted' rows. If you don't want to lose data
then move it to an archive table before deleting from the main table
or better still keep a separate transaction log table so you don't
have to bother with 'moving' data around.

Jamie.

--

 

[Jamie Collins]

It is not good practice to disable the table from anything.

Jet security provides (or used to) for a number of privileges
including DELETE and DROP. Why do you think it is bad practice to use
permissions to control what users can legitimately do with database
objects?

Jamie.

--

 

[Guest]

Yes I actually use it all the time. Mainly for lookup tables. Data held on
Tables
referring to Lookups for 10 plus years e.g. User Id recorded against many
rows held, for Audit purposes and staff are mobile. Can only delete these
once the main file has no reference to any lookup used.

Maintenance no big deal SOP or System Standards whichever you wish to call it.
Plus so far it has only been me doing it. I run with full "test" backend
databases for testing development and the users only get .mde front ends to
run against the "live"
databases.

Do appreciate the point though. It requies discipline.

 

[Jamie Collins]

Yes I actually use it all the time. Mainly for lookup tables.
so far it has only been me doing it

Figures. Sounds you've designed things to suit yourself rather than
standard practice i.e. there is reasonable expectation that 'deleted'
rows get deleted. Suggestions on how to fix this include making them
valid-time state tables (i.e. history tables with a start- and end
date pairs on each row) and wrapping the logic (Deleted = 'N', NOW()
BETWEEN start_date AND end_date, etc) into VIEWs and (as punishment
<g>) take on yourself the maintenance of these VIEWs each time the
base tables change.

Jamie.

--

 

[Jamie Collins]

Suggest you use a "Deleted/Disabled" field (boolean ((Yes/No)) in your table.

It not only has an impact on SQL DML (SQL queries and updates), your
proposed design also makes SQL DDL harder to write. Take a simple real
life example:

CREATE TABLE Parts (
part_number CHAR(8) NOT NULL PRIMARY KEY,
part_name VARCHAR(15) NOT NULL,
is_deleted CHAR(1) NOT NULL, CHECK (is_deleted IN ('N', 'Y'))
);

Consider a common sense rule that says all current (is_deleted = 'N')
parts must have unique names while allowing 'deleted' part's names to
be reused. You can't use a UNIQUE constraint, must use a table-level
CHECK constraint e.g.

ALTER TABLE Parts ADD
CONSTRAINT part_name__unique_for_non_deleted
CHECK (
NOT EXISTS (
SELECT T2.part_name
FROM Parts AS T2
WHERE T2.is_deleted = 'N'
GROUP BY T2.part_name
HAVING COUNT(*) > 1));

Now consider that the natural key for a lookup table is commonly the
sole data column and you have one of those tricky situations where
your primary key (lowercase) requires a table-level CHECK constraint :
(

Jamie.

--

 

[Guest]

Each to his own - no multiple views to data, no query merges to pick up
historic data, and all reports based on date from - to, records on a table
than need to be deleted can and are deleted along with their related records
on other tables. Some
tables contain records that should not and cannot be deleted. Dcount takes
care of records that can be deleted from those tables - i.e. the record has
been created but has never been used in any of its relationships. However
your appraisal of systems you know nothing about is an interesting concept,
being how you would do that rather than what suites the particular situation.

I will stick with Never make a table that the records cannot be deleted,
control this via logic rather than brawn.

I believe we have digressed enough.

 

[Jamie Collins]

Each to his own

That's a great philosophy to have when you work in a team of one.

your appraisal of systems you know nothing about is an interesting concept

Thanks. The trick is to look for symptoms of commonly committed flaws.
Asides such as "so far it has only been me doing it" can be very
revealing. Did you run your idea past a mentor before implementing
it?

Think about this:

REVOKE DELETE FROM Mytable FROM ThisUserGroup

is SQL-92 SQL DCL code. You rolled your own.

When you post a proposal in a public forum you should expect some
appraisal/critique...

I believe we have digressed enough.

I have experience of your proposed design in at least two systems that
come to mind and both were a screaming pain. Sharing my experience as
a warning is no digression IMO.

Dcount takes care of records that can be deleted from those tables - i.e. the record has
been created but has never been used in any of its relationships.

Not as well as the ON DELETE NO ACTION referential action does.

Jamie.

--