Re: A question about trigger fucntion syntax

[stan <stanb@xxxxxxxxx>]

I trimmed this thread quite a bit.

Thanks to the help I have received, I am making progress. I have looked a the
recommended documentation, and I believe I am close. I have utilized the
"debugging printf" capability here. Here is where I am. I have the following
function create.


DROP TRIGGER validate_task_trig ON task_instance ;

DROP FUNCTION check_permission() ;


CREATE FUNCTION check_permission()
RETURNS trigger AS $$
DECLARE _permit boolean;
BEGIN
        SELECT
                permit INTO _permit
        FROM
                permitted_work
        WHERE
                NEW.employee_key = OLD.employee_key
        AND
                NEW.work_type_key = OLD.work_type_key;
        RAISE NOTICE 'New employee_id % NEW.work_type_key % _permit = %', 
			NEW.employee_key ,
			NEW.work_type_key ,
			_permit ;
        if NOT _permit THEN
                RAISE NOTICE 'No permission record';
                RAISE EXCEPTION 'No permission record';
	ELSE
                RAISE NOTICE 'Found Permission Record';
        END IF;
        if _permit = FALSE THEN
                RAISE NOTICE 'Permission Denied';
	ELSE
                RAISE NOTICE 'Permission Granted';
        END IF;

        return NEW;
END;
$$ 
LANGUAGE PLPGSQL;

CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance
    FOR EACH ROW EXECUTE FUNCTION check_permission();


Now the issues, currently seems to be that nothing is getting assigned to
_permit. Here is the output of a run with 0 records in the permitted_work
table.

NOTICE:  New employee_id 1 NEW.work_type_key 8 _permit = <NULL>
NOTICE:  Found Permission Record
NOTICE:  Permission Granted
INSERT 0 1

so it appears that nothing is getting assigned to _permit. Also should I be
checking for _permit as NOT NULL in the first if clause?

Thanks for all the had holding on this. Brand new application for me.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin