Let's imagine the following scenario:
CREATE TABLE invalido (
ds_valor character varying(255)
)
CREATE TABLE email (
email character varying(255)
)
I now need a trigger that every time I run a UPDATE or INSERT in the email table it checks if the value is in the invalido table, if it is then it should replace what would be inserted in the email table for invalid email .
First, create a function that returns type trigger :
CREATE FUNCTION validar_email()
RETURNS trigger AS '
BEGIN
IF EXISTS (SELECT
FROM invalido
WHERE ds_valor = NEW.email) THEN
NEW.email := ''email invalido'';
END IF;
RETURN NEW;
END' LANGUAGE 'plpgsql'
Then create a trigger that fires before INSERT and UPDATE that will allow the value to be changed and execute the above function:
edit:
CREATE TRIGGER trg_before_iu_email
BEFORE INSERT OR UPDATE ON email
FOR EACH ROW
EXECUTE PROCEDURE validar_email()
Hello ... @Sorack yes you can do ... yes ...
Just put an Update inside the trigger eg:
CREATE OR REPLACE FUNCTION public.func_before_update_c0220 ( )
RETURNS trigger AS $body$ begin
if new.nome_cliente <> old.nome_cliente then
update public.table_02
set nome_cliente = new.nome_cliente
where id_cliente = new.id_cliente;
end if;
return new;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE CALLED ON NULL
INPUT SECURITY INVOKER COST 100;