Postgresql Trigger apos update

Asked

Viewed 616 times

3

Let’s imagine the following scenario:

CREATE TABLE invalido (
   ds_valor character varying(255)
)

CREATE TABLE email (
    email character varying(255)
)

now I need a trigger that every time I run a UPDATE or INSERT on the table email check if the value is in the table invalido, if you are then you should replace what would be inserted in the table email for invalid email.

postgresql trigger

2 answers

3


First, create a function that returns the 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 shoots before the INSERT and of UPDATE which will allow the value to be changed and perform the above function:

Edit:

CREATE TRIGGER trg_before_iu_email
BEFORE INSERT OR UPDATE ON email
FOR EACH ROW
EXECUTE PROCEDURE validar_email()

  • syntax error at or near "UPDATE", removing the UPDATE wheel...

  • tries to put BEFORE INSERT OR UPDATE and confirms if it worked, please

  • Approves then ^^

  • @Sorack I know this post is a little old but I can apply this idea if I have the following scenario, I have a table 01 with id_client and client name_table and the table 02 also with id_client and client name_table, i need that if I change the record of the column name_client in table 01 the record that has the same id_client in table 02 change the record name_client too, it is possible?

  • @R.Santos good question. You would have to do a search to see if you can make changes in another table with trigger in the Postgres. But you can open a question and we’ll take a look

  • I opened one like this: https://answall.com/questions/204026/trigger-que-actualizze-registro-em-uma-tabela if you happen to find any ideas I thank you, it doesn’t have to be necessarily via Rigger, if you have any idea that I can do this is already a way :)

Show 1 more comment

1

Hello... @Sorack yes you can do... yes...

only put an Update inside Trigger ex:

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;

Browser other questions tagged postgresql trigger

You are not signed in. Login or sign up in order to post.