[GENERAL] Howto prevent write based on date
I'm a newbie to database admin, but I'm not afraid to try - but this one has me stumped. I'm using SQL-Ledger as a front end to postgresql 7.4.2. on (mostly) Debian Testing, i386. My fiscal year is over and I would _like_ to prevent any changes to the data from last year. I looked/searched in the manual, but I don't even know what to call what it is that I'm trying to do! "lock" has another meaning for databases. :) "Write" seems to bring up lots of user-related stuff. So, I'm hoping one of you geniuses can tell me where to look, what to look for, or how to do it. *smile* Thanks! Philip, wanabe-admin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 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 _tg1 BEFORE UPDATE OR DELETE ON 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
