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
Re: [GENERAL] Howto prevent write based on date
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 :) 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[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
Fmiser [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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. A simple trigger would work well. A rule could do it too, but rules can have gotchas if you don't know exactly what you are doing, and triggers give finer grained control, because you get to use procedural logic. 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Howto prevent write based on date
On Wednesday 04 May 2005 09:35 am, Fmiser wrote: 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 If I remember correctly SQL-Ledger has just such an option in the administrator options. This would save you from having to play around with the back end. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Howto prevent write based on date
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 :) 2005/5/4, Fmiser [EMAIL PROTECTED]: I'm a newbie to database admin, but I'm not afraid to try - but this onehas 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 whatit 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 tolook 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
