Re: After Insert Trigger

PRAVEEN MOHANAN wrote: > I am calling a procedure from a trigger. Now i want to send the > few fields of the record which i am inserting into the table to the > procedure. > eg: > > CREATE TRIGGER abcd INSERT ON table_abc > REFERENCING NEW AS new > AFTER (EXECUTE PROCEDURE oulpr_iclocation(:new.val1, > :new.val2,....)); I don't think the colons are necessary. I'm not sure they're even legal, but that's a different issue. > I am able to call the procedure in AFTER clause but with out any > parameters . The record "new." stores the value only when "FOR EACH > ROW" is used. I want to execute the proceudure only if the record > is inserted successfully. Well, you're stuck. The BEFORE and AFTER clauses are for operations before and after the set of (possibly one, possibly zero, possibly many) rows is inserted. The only way to deal with each row is with the FOR EACH ROW clause. I forget whether that is triggered before or after the row is inserted. I'd guess it is after to allow serial numbers to be inserted and the values to be propagated, but I could be indulging in wishful thinking. The manual should, and probably does, cover the point. More specifically, you cannot refer to the NEW values in the BEFORE and AFTER triggers because there is not, necessarily, a single value. Consider: INSERT INTO Table_ABC SELECT * FROM Table_DEF WHERE val1 > 0; There could be hundreds of rows in Table_DEF which satisfy the criterion, but the BEFORE trigger will be executed once, the after trigger will be executed once, and the FOR EACH ROW trigger is what is executed many times. Yours, Jonathan Leffler (jleffler@earthlink.net) #include <many-aliases.h>