Re: Howto prevent write based on date - Mailing list pgsql-general

Franco Bruno Borghesi wrote:
> You could write a trigger like this:
>
>
> CREATE OR REPLACE FUNCTION checkDate() RETURNS TRIGGER LANGUAGE 'plpgsql' AS '
> DECLARE
>     limitDate DATE DEFAULT current_date-''1 year''::INTERVAL;
> BEGIN
>     IF (OLD.date<=limitDate) THEN
>         RAISE EXCEPTION ''Cannot change record.'';
>     END IF;
>
>     RETURN NEW;
> END;
> ';
>
> CREATE TRIGGER xxxx_tg1 BEFORE UPDATE OR DELETE ON xxxx FOR EACH ROW
> EXECUTE PROCEDURE checkDate();
>
> This should do the job :)

Franco's trigger function should do the job just fine, but speaking from
experience you'll want to take further steps.

Take a backup of the database, restore it to another system and also
burn a copy to a CD.

If the auditors come round it's simple to explain what you've done and
demonstrate the data on the CD and backup system match. It also means
that should any changes occur to your historical data despite your
precautions you can prove that this happened.

--
   Richard Huxton
   Archonet Ltd