Обсуждение: Prevent from Deletion

Hi,
I have tables that have default records that must not be deleted or
modified.
Is there an easy way to do this. Like setting a trigger on the Primary
key value ?

Alex

Alex <alex@meerkatsoft.com> writes:

> Hi,
> I have tables that have default records that must not be deleted or
> modified.
> Is there an easy way to do this. Like setting a trigger on the Primary
> key value ?

You could do this--create ON UPDATE and ON DELETE triggers that look
for distinguishing features of the default records (primary key value
or whatever) and RAISE ERROR if they match.  They'll be executed for
every UPDATE and DELETE on the table, which may or may not be a
performance issue for you...

-Doug

Alex wrote:

> Hi,
> I have tables that have default records that must not be deleted or
> modified.
> Is there an easy way to do this. Like setting a trigger on the Primary
> key value ?
>
Thatt'd be the way.

I was just wondering if using some rewrite rules is not better:

cnagy=> create table test(col1 int4);
CREATE TABLE
cnagy=> insert into test values (0);
INSERT 47709303 1
cnagy=> insert into test values (1);
INSERT 47709304 1
cnagy=> insert into test values (2);
INSERT 47709305 1
cnagy=> insert into test values (3);
INSERT 47709306 1
cnagy=> create or replace rule test_rule as on delete to test where col1
in (1, 2) do instead nothing;
CREATE RULE
cnagy=> select * from test;
 col1
------
    0
    1
    2
    3
(4 rows)

cnagy=> delete from test;
DELETE 2
cnagy=> select * from test;
 col1
------
    1
    2
(2 rows)

Is there any reason not to use rewrite rules here ? Might be more
performant than triggers...

Cheers,
Csaba.

On Mon, 2003-09-01 at 05:29, Doug McNaught wrote:
> Alex <alex@meerkatsoft.com> writes:
>
> > Hi,
> > I have tables that have default records that must not be deleted or
> > modified.
> > Is there an easy way to do this. Like setting a trigger on the Primary
> > key value ?
>
> You could do this--create ON UPDATE and ON DELETE triggers that look
> for distinguishing features of the default records (primary key value
> or whatever) and RAISE ERROR if they match.  They'll be executed for
> every UPDATE and DELETE on the table, which may or may not be a
> performance issue for you...
>
> -Doug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

On Mon, Sep 01, 2003 at 17:50:41 +0200,
  Csaba Nagy <nagy@ecircle-ag.com> wrote:
> I was just wondering if using some rewrite rules is not better:

I think that depends on what you want to have if somebody trys it.
Using do instead nothing will make the attempt look like it succeeded
when it didn't.

On Sun, 2003-08-31 at 22:07, Alex wrote:
> Hi,
> I have tables that have default records that must not be deleted or
> modified.
> Is there an easy way to do this. Like setting a trigger on the Primary
> key value ?

How about revoking DELETE and UPDATE privileges from all users
except the DBA?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Those who would give up essential Liberty to purchase a little
temporary safety, deserve neither Liberty nor safety." or
something like that
Ben Franklin, maybe

DBA's never would make the mistake of deleting a record that is a key
part of a design, of course.

 ;-)



Ron Johnson wrote:

>On Sun, 2003-08-31 at 22:07, Alex wrote:
>
>
>>Hi,
>>I have tables that have default records that must not be deleted or
>>modified.
>>Is there an easy way to do this. Like setting a trigger on the Primary
>>key value ?
>>
>>
>
>How about revoking DELETE and UPDATE privileges from all users
>except the DBA?
>
>
>