CODEWITHSUNDEEP
postgresqldatabase-trigger
LyessD
LyessD

Reputation: 221

How to use trigger with 3 different tables

Dear Fellow StackOverFlow-members,

I have 3 tables. bdc(bdc_id, bdc_name, bdc_gc), stt(stt_id, stt_gc), bts(bts_id, bts_pmv).

I want if stt_gc = 'Checked' then set bdc_gc = 'Sent' and bts_pmv = 'To do'

I use Postgresql 11 and beginning with triggers/stored procedures I tried to check with if condition stt_gc value and matching with the right bdc_gc bts_pmv according to their primary key. 

create or replace function before_stt_gc() returns trigger
  language plpgsql
as
$$
begin

    if new.stt_gc='Checked' then
      select bdc_gc from bdc
      where new.stt_id = bdc_id;
      doe_gc_bts= 'Sent';
      select bts_pmv from bts
      where new.stt_id = bts_id;
      bts_pmv = 'To do'
    end if;
  end;
$$;

create trigger before_stt_gc_trigger before insert or update on stt
  for each row 
  execute procedure before_stt_gc();

Obviously if I'm here it's because my code is totally wrong... I want to learn from this, so if possible, explain me what I'm doing wrong here, or if my approach is lacking insight

Upvotes: 0

Views: 38

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31746

I presume you are looking for updates within the IF statement

if new.stt_gc='Checked' then
  update  bdc  set bdc_gc = 'Sent'
  where new.stt_id = bdc_id;

 UPDATE bts SET bts_pmv = 'To do'
  where new.stt_id = bts_id;

end if;

Upvotes: 1

Related Questions