i'm posting questions like this to the postgresql-general list,
and am deafened by the silence, so i'll try here. (you folks
don't know when to quit when it comes to helping newbies... :)
postgresql 7.0.3 / potato
direct from the documentation (.../postgresql-doc/html/user/c40874340.htm)
here's a function/procedure and trigger to call it:
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
BEGIN
-- Check that empname and salary are given
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := ''now'';
NEW.last_user := getpgusername();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
and when inserting to the table, the trigger is activated, the
function is called, and the pseudo table NEW.* is correctly
populated with values intended for the insertion.
when i expand this and try to add a SELECT on other tables, or an
UPDATE to another table or two, postgresql gripes about
NEW used in non-RULE query...
as if you can only rely on NEW.* in functions and sql statements
called by rules, as opposed to triggers.
so --
how can i call UPDATE or SELECT within a trigger-activated
function BEFORE the insert? (or, what am i not grokking?)
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
[EMAIL PROTECTED]
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
