2

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
  • Unrelated, but: there are many ready-made audit triggers out there. See e.g. here or here or here Commented Mar 6, 2018 at 12:45

1 Answer 1

1

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

1
  • That and there was one line with product.new instead of product_new Commented Mar 6, 2018 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.