update column date to today if any value is modified

Question

I need to modify the column last_update_date to today's date only if any other value of the table Table1 is modified. The code throws an error if the value to be modified is the column last_update_date.

Here is my code:

CREATE FUNCTION date_change() RETURNS TRIGGER AS $date$

BEGIN
    IF (TG_OP = 'UPDATE') THEN
            UPDATE erp.tb_customer SET last_update_date = current_date;
    ELSIF (OLD.last_update_date != NEW.last_update_date) THEN
        RAISE EXCEPTION 'Error not possible to modify the date';
    END IF;

and why didn't you post the code see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — nbk, May 12 '22 at 13:22
This is a trigger function. Did you create a trigger that uses it? — Panagiotis Kanavos, May 12 '22 at 13:39
write a trrigger function SEE https://stackoverflow.com/questions/9556474/how-do-i-automatically-update-a-timestamp-in-postgresql — nbk, May 12 '22 at 13:47
@nbk I am not used to write advanced sql code... why is my code not working? — Gus Cortejana, May 12 '22 at 13:54
@PanagiotisKanavos I don't know much about trigger. In my case, the date needts to be updated ONLY IF someone modifies any other value of the other variables... — Gus Cortejana, May 12 '22 at 13:59

nbk · Answer 1 · 2022-05-12T15:39:52.313

There are some problems.

you need a trigger

postgres thows an error when you send your code.

the Syntax can look like you see below

CREATE tABLE erp(myid int , last_update_date timestamp)

CREATE FUNCTION date_change() RETURNS TRIGGER AS $date$

BEGIN
    IF (TG_OP = 'UPDATE') THEN
            NEW.last_update_date = current_date;
    ELSIF (OLD.last_update_date != NEW.last_update_date) THEN
        RAISE EXCEPTION 'Error not possible to modify the date';
    END IF;
END;
$date$ language 'plpgsql';

CREATE TRIGGER erp_audit
BEFORE UPDATE ON  erp
    FOR EACH ROW EXECUTE FUNCTION date_change();

db<>fiddle here

As INSTEAD OF TRIGGER, it would look like, but that s for much more extensive functionality

CREATE tABLE erp(myid int , last_update_date timestamp)

CREATE VIEW erp_view AS SELECT * FROM erp;

CREATE FUNCTION date_change() RETURNS TRIGGER AS $date$

BEGIN
    IF (TG_OP = 'UPDATE') THEN
            NEW.last_update_date = current_date;
    ELSIF (OLD.last_update_date != NEW.last_update_date) THEN
        RAISE EXCEPTION 'Error not possible to modify the date';
    END IF;
END;
$date$ language 'plpgsql';

CREATE TRIGGER emp_audit
INSTEAD OF UPDATE ON erp_view
    FOR EACH ROW EXECUTE FUNCTION date_change();

db<>fiddle here

run the fiddle without the view and you get an error that tables can't have a instead of trgger — nbk, May 12 '22 at 15:15
Then don’t use instead of. Never needed it for something like this — Frank Heikens, May 12 '22 at 15:34

update column date to today if any value is modified

1 Answers1