Thread: 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? > > >