1

I have created an audit trigger for table "product" that should insert data into "log_product".

CREATE FUNCTION public.insert_log_product()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF 
ROWS 0
AS $BODY$
BEGIN
IF (TG_OP = 'UPDATE')
THEN
    INSERT INTO log_table(
      operation,
      product_type_new, 
      product_type_old,
      product_name_new,
      product_name_old,
      vendor_id,
      available_quantity_unit_new,
      available_quantity_unit_old,
      delivery_time_in_days_new,
      delivery_time_in_days_old,
      change_date
    )
    VALUES(
      'UPDATE',
      new.product_type,
      old.product_type,
      new.product_name,
      old.product.name,
      new.vendor_id,
      old.vendor_id
      new.available_quantity_unity,
      old.available_quantity_unity,
      new.delivery_time_in_days,
      old.delivery_time_in_days,
      now()
    );

END IF;
RETURN NULL;
END;

 $BODY$;

But I get an error message:

org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "product"

The function is created using PGadmin 4.2.

CREATE TRIGGER insert_log_product_trigger
AFTER INSERT OR DELETE OR UPDATE 
ON public.table
FOR EACH ROW
EXECUTE PROCEDURE public.insert_log_product();

The requirement for our audit table is to have each column separately, old and new.

1

You are trying to insert 42 values into 41 columns. Check vendor_legal_id.

  • That and there was one line with product.new instead of product_new – Marko Taht Mar 6 '18 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.