# Preventing deletes at the database level

## Create a function for a trigger that will refuse to delete 'default'

CREATE OR REPLACE FUNCTION protect_default() RETURNS TRIGGER as $protect_default$
  BEGIN
    IF OLD.name = 'default' THEN
      RAISE EXCEPTION 'Cannot delete default test';
    END IF;
    RETURN OLD;
  END;
$protect_default$ LANGUAGE plpgsql;

## Create a table that uses that function at ON-DELETE time

CREATE TABLE test (
    id INTEGER PRIMARY KEY, 
    name varchar(40) NOT NULL
    );
CREATE TRIGGER protect_default BEFORE DELETE ON test
  FOR EACH ROW EXECUTE FUNCTION protect_default();

## Fill the table with values

INSERT INTO test (id, name) 
VALUES
 (1, 'default'), 
 (2, 'test2'), 
 (3, 'test3');

## Try to delete "default"

DELETE FROM test WHERE name = 'default';
DELETE FROM test WHERE name = 'test2';

## Run-time example:

pulp=> CREATE OR REPLACE FUNCTION protect_default() RETURNS TRIGGER as $protect_default$
  BEGIN
    IF OLD.name = 'default' THEN
      RAISE EXCEPTION 'Cannot delete default test';
    END IF;
    RETURN OLD;
  END;
$protect_default$ LANGUAGE plpgsql;
CREATE FUNCTION

pulp=> CREATE TABLE test (
    id INTEGER PRIMARY KEY, 
    name varchar(40) NOT NULL
    );
CREATE TRIGGER protect_default BEFORE DELETE ON test
  FOR EACH ROW EXECUTE FUNCTION protect_default();
CREATE TABLE
CREATE TRIGGER

pulp=> INSERT INTO test (id, name) 
VALUES
 (1, 'default'), 
 (2, 'test2'), 
 (3, 'test3');
INSERT 0 3
pulp=> select * from test;
 id |  name   
----+---------
  1 | default
  2 | test2
  3 | test3
(3 rows)

pulp=> DELETE FROM test WHERE name = 'default';
ERROR:  Cannot delete default test
CONTEXT:  PL/pgSQL function protect_default() line 4 at RAISE
pulp=> select * from test;
 id |  name   
----+---------
  1 | default
  2 | test2
  3 | test3
(3 rows)

pulp=> DELETE FROM test WHERE name = 'test2';
DELETE 1
pulp=> select * from test;
 id |  name   
----+---------
  1 | default
  3 | test3
(2 rows)

pulp=>