August 28th, 2003, 07:51 AM
looking for Filter/ Replacing function/trigger ...
Maybe someone knows a solution for the following problem or has written a function/ trigger allready..
1. The Problem:
I'm having some kind of a data security (privacy) problem. I have in my tables to many indicators doing the following by hand and so I tried to write a short trigger, function (okay I'm a newbie in database-administration). When I put in the output-database a value less than 4 into any field at any row, this value has to get 0 automaticly.
2. My first idea was write a Trigger/Function - Construction for the database postgresql 7.3 database
CREATE FUNCTION pg_clean()RETURNS TRIGGER AS'
if NEW=3 THEN set NEW=0;
if NEW=2 THEN set NEW=0;
if NEW=1 THEN set NEW=0;
CREATE TRIGGER clean AFTER INSERT OR UPDATE
FOR EACH ROW
EXECUTE PROCEDURE pg_clean();
3. error: NEW used in none query rule
(looks like a deadlook as far as I read the manuals)
4. Has somebody a solution?
best regards and thx
August 28th, 2003, 09:02 AM
when you do NEW you have to specify the attribute name of the triggered record that you're referring to. For instance, if the table has two columns, pk_val and data_val you would have to do
NEW.pk_val = 3 THEN NEW.pk_val = 0
I don't think you need to say RETURN NEW because you did return type as TRIGGER, but you may want to do RETURNS OPAQUE AS ''; in stead, that's just a matter of preference though to me it's easier to follow that way. I think both are supported after 7.0.x
August 28th, 2003, 10:05 AM
I'm looking more for a dynamic solution
They way you suggest is of cause right, but I still have the problem that actually in the database are 24 tables with maybe more than 600 columns, doing all this by hand is really hard work. Is their no other way than doing this routine like:
IF NEW.social_aid=3 THEN social_aid=0;
I still believe there is a more sexy functionů