PostgreSQL-Syntax error at or near "trigger"-CodePudding

I'm trying to create a procedure/trigger which when modifying a field from a 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 it 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, but I'm getting a syntax error at "returns trigger". I don't know why. Any ideas?

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

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.