PostgreSQL-Update last_update_date to current if modif at table-CodePudding

I'm trying to create a function with a trigger which when modifying a field from table tb_customer, the attribute last_update_date should be updated to the current_date of the modification. If an user tries to enter a value in attribute last_update_date, an error should be raised with a message, and not allow this insert.

The table code for creation is:

 CREATE TABLE erp.tb_customer    (
    cust_no          CHARACTER(5) NOT NULL,
    cust_name        CHARACTER VARYING(50) NOT NULL,
    cust_cif         CHARACTER VARYING(150) NOT NULL,
    last_updated_by  CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date DATE NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY (cust_no)
  );

So far, I have this code:

CREATE OR REPLACE FUNCTION modif_update_date_tracker() 
RETURNS trigger AS $$
BEGIN
    IF INSERT AT last_update_date THEN
        RAISE EXCEPTION 'Not possible to update this field'
    END IF;
    NEW.last_update_date := current_time;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql
  ------------------------------------------------------------------------------------------------
  -- Create trigger 1
  ------------------------------------------------------------------------------------------------
CREATE TRIGGER trigger_modif_update_date_tracker
BEFORE UPDATE
ON             tb_customer
FOR EACH ROW
EXECUTE PROCEDURE modif_update_date_tracker();

CodePudding user response：

BEGIN
  IF OLD.last_update_date!=NEW.last_update_date THEN
        RAISE EXCEPTION 'Not possible to update this field';
   END IF; 
   NEW.last_update_date := CURRENT_TIMESTAMP;
    RETURN NEW;
END;

CodePudding user response：

When a user is not allowed to update a column, you should REVOKE him from doing so. You don't need a trigger for that.

testing code is something you can do yourself. But I can already tell you that UDAPTE is not a valid value for TG_OP.

A complete trigger example can be found in the manual.

CodePudding user response：

It needs to be created as a FUNCTION, not as a PROCEDURE. There are other problems as well, but I don't know which ones are real and which ones you introduced while retyping it.