how to make undeleted record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

causacn@gmail.com - 01 Dec 2005 13:59 GMT

Hi all,

I want to have some of records in table to be undeleted?
how can i do that?
for instance, keep first 20 records of table undeleted or read only for
outside access
but DBA or users who have DBA or higher level rights can do delete or
change operations.

Many thanks!

Reply to this Message

Brian Tkatch - 01 Dec 2005 14:08 GMT

I am not sure what you mean by undelete.

However, if you only want to let some people do a DELETE operation, and
others not, only GRANT DELETE to the users that require it.

If you want to allow some records to be DELETEable by everyone, and
others to only be DELETEable by some, CREATE two TABLEs, one for each
set, and GRANT DELETE to all users on one, and to only some users on
the second one. Then, CREATE a VIEW that is a UNION ALL on both TABLEs,
and have the users access the view rather than the individual TABLEs.

B.

Reply to this Message

Knut Stolze - 01 Dec 2005 16:56 GMT

> I am not sure what you mean by undelete.
>
[quoted text clipped - 6 lines]
> the second one. Then, CREATE a VIEW that is a UNION ALL on both TABLEs,
> and have the users access the view rather than the individual TABLEs.

Or you can use a view including the WITH CHECK OPTION. Deleting against the
view ensures that at least 20 records remain in the table.

Or you stick with triggers.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Brian Tkatch - 01 Dec 2005 17:33 GMT

> Or you can use a view including the WITH CHECK OPTION. Deleting against the
> view ensures that at least 20 records remain in the table.

How would you do that?

> Or you stick with triggers.

A TRIGGER would definitely of service here.

...but...IMNHSO, i don't like the use of TRIGGERs for major things. If
the second TABLE was populated with DELETEs, but the second TABLE was
not used by the system, a TRIGGER makes a lot of sense as it is more
data management. But to have two TABLEs, and have a TRIGGER keep them
in sync for actual use (through a view), the application or stored
PROCEDURE should handle it. Otherwise it tends to get real messy.

IOW, the actual data model should not use TRIGGERs, only management
features like an non-natural Id COLUMN or a history TABLE (for "just in
case" purposes) or rights that cannot be otherwise implemented, or
auditing, etc should use TRIGGERs.

But that's just my opinion. :)

Based on the ugliness i've seen when people begin to rely on TRIGGERs
to populate other TABLEs, and then have to code around them.

B.

Reply to this Message

Knut Stolze - 02 Dec 2005 10:21 GMT

>> Or you can use a view including the WITH CHECK OPTION. Deleting against
>> the view ensures that at least 20 records remain in the table.
>
> How would you do that?

Create the view in such a way that the underlying table must have at least
those 20 records. And if that's not the case, the view does not show
anything. So deleting the 20th row would result in no rows shown in the
view and, thus, the check option being violated.

Granted, that's a rather strange way but it should work. After all, that's
just usual SQL stuff. ;-)

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Brian Tkatch - 02 Dec 2005 15:18 GMT

>Create the view in such a way that the underlying table must have at least those 20 records.

OK. Could you provide a quick example. I am curious how a view
guarantees a certain amount of rows.

>Granted, that's a rather strange way but it should work. After all, that's just usual SQL stuff. ;-)

Although, a good design (usually) removes the "usual" SQL stuff, so
things can be a bit more stright forward.

B.

Reply to this Message

Knut Stolze - 02 Dec 2005 16:27 GMT

>>Create the view in such a way that the underlying table must have at least
>> those 20 records.
>
> OK. Could you provide a quick example. I am curious how a view
> guarantees a certain amount of rows.

SELECT ...
FROM <tab>
WHERE ... AND
( SELECT COUNT(*)
FROM <tab> ) >= 20

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Brian Tkatch - 02 Dec 2005 16:32 GMT

Oh, i see. That's easy, i guess i never thought of that. :)

Hmm.. This doesn't use any order though. IOW, it'll keep *any* twenty
records.

B.

Reply to this Message

Knut Stolze - 02 Dec 2005 16:57 GMT

> Oh, i see. That's easy, i guess i never thought of that. :)
>
> Hmm.. This doesn't use any order though. IOW, it'll keep *any* twenty
> records.

That's right. I don't know if the OP had any special tuples in mind.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message