Search code examples
sqlpostgresqltriggersprocedure

PostgreSQL-Update last_update_date to current if modif at table


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();

Solution

  • 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;