Fire trigger on update of columnA or ColumnB or ColumnC
Set up the scene
begin;
create temp table account_details(email text primary key, username text, password text);
insert into account_details(email, username, password) values('a.com','b','c'),('b.com','d','e');
commit;
create function:
CREATE OR REPLACE FUNCTION notify_insert_account_details()
RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
RAISE NOTICE 'hello world';
END
$$;
create trigger:
CREATE TRIGGER trigger_update_account_details
AFTER UPDATE ON account_details
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email
OR OLD.username IS DISTINCT FROM NEW.username
OR OLD.password IS DISTINCT FROM NEW.password)
EXECUTE PROCEDURE notify_insert_account_details();
Trying to update:
update account_details set username = 'username1' where email = 'a.com';
The result: notify_insert_account_details() fired. but didn't execute the update clause.
NOTICE: hello world
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function notify_insert_account_details()
My question: how sophisticated this procedure/function notify_insert_account_details() Can become, let's say linked within 3 table. Can anyone showcase an example/demo?
