Re: sub query constraint

Yudie Pg wrote:
>>One way to do this is to add a write_access column to actions and use
>>a constraint to force it to be true.
>>Create a UNIQUE key of
>>(name, write_access) for user_data and then add a FOREIGN KEY
>>reference from (name, write_access) in actions to (name, write_access)
>>in user_data.
>
>
>
> Yes the name must unique indexed but couldn't force the write_access
> to always 'true'.
> I may suggest create a trigger function to validate insert to table actions:
>
> CREATE OR REPLACE FUNCTION validate_actions_insert() RETRUNS OPAQUE AS '
> DECLARE
> rs RECORD;
>
> BEGIN
> SELECT INTO rs * FROM user_data WHERE name = NEW.user and write_access = 't';
>
> IF NOT FOUND THEN
> RAISE EXCEPTION ''writing access forbidden for user '', NEW.user;
> END IF;
>
> RETURN NEW;
> END;
> ' LANGUAGE plpgsql;
>
> CREATE TRIGGER tg_actions BEFORE INSERT OR UPDATE ON actions
> FOR EACH ROW EXECUTE PROCEDURE validate_actions_insert();
>
> You may need create another trigger for table user_data before update
> for reverse validation.
>
Bruno and Yudie,
Thanks for the replies. I will read up on triggers and give that a try.

Thanks,

Dale