Re: Howto prevent write based on date

rumor has it that Richard wrote:

> 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 :)

I feel like I'm 1 meter tall and the wave on the beach are more than 3
meters high...

Thank you for the code.

It looks like it would need to be a part of any access to the database,
so I imagine I would have to figure out where to put it into the
front-end code. Is this correct?

> 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.

Ahh, that is a good idea! A database dump is a part of my daily backup.

I guess I could also make a read-only copy of the year-end as a second
database on the same system. That could make it easy to keep an eye on
the main database so I (hopefully) spot any ripples that reach back to
last year.

Thanks for the help! Philip