Re: Restricting Row Deletions

In a previous article, berli_at_switch.ch (Martin Berli) says:

>Has anyone an idea how disallow deletes of more than one row of a table?
>
>I can imagine how to do this on the application side: turning the delete
>request into a select request and count the number of rows returned.
>
>But if one wants to do this in the database, with triggers, this might be
>tricky...
>
>Any ideas?
>
> Martin Berli
>
>------------------------------------------------------------------------
>Internet: berli_at_switch.ch | SWITCH, Swiss Academic and Research Network
>Phone : +41 1 268 1540 | Limmatquai 138
>Fax : +41 1 268 1568 | CH-8001 Zurich
>
I tried to answer this before, but was interrupted, so I'll try again. Use a packaged variable to store a 'remove_state' variable.

package state is
  remove_state boolean;
end state;

package body state is
begin
  remove_state := TRUE;
end state;

trigger bd_table
before delete on table
for each row
begin
  if state.remove_state then
    state.remove_state := FALSE;
  else
    /* raise an exception */
  end if;
end bd_table;

The remove_state variable will be set to TRUE on the first call to the package. If you have other conditions where it should be set to TRUE, you would do that.

Hope this works.

-- 
Diana Tracy, System Designer		-- Excitement, Adventure
bs794_at_cleveland.Freenet.Edu		-- and Really Wild Things