Re: INSERT trigger updating the inserted row
Hi,
Kai_Shen@acer.de wrote:
> Is it possible (and how) to set a column of a newly inserted row to a
> calculated column?
> I wanted to something like
>
> create trigger po_date> INSERT ON table1
> REFERENCING new AS post
> FOR EACH ROW WHEN (post.supplier ="123456")
> (UPDATE table1 SET date_required = TODAY + 14 UNITS DAY)
>
> I know this is not allowed. I have to do it on SQL base, since we cannot
> change the application,
> but how can I achive it?
>
> Very urgent!
>
> TIA,
> Kai
Here is an example of a trigger making an update on the inserted row. Hope
you can use it.
Bjarne
Alm. Brand
Denmark.
Insert trigger with update :
|-------------------------------------------------------------|
|create trigger dw_skadeins_tr insert on skade |
|referencing new as ny |
|for each row ( execute procedure dw_skadetilhoer_sp( |
|ny.dw_dato, ny.skade_nr, ny.police_nr, ny.skade_serie_nr, |
|ny.skade_tp ) |
|into skade.kontor_kd, skade.kunde_tp, skade.prod_kd); |
|-------------------------------------------------------------|
procedure :
|-------------------------------------------------------------|
|DROP PROCEDURE informix.dw_skadetilhoer_sp |
|; |
|-------------------------------------------------------------|
|---------------- |
|-- Creation Date : 1999-05-26 14:22:13.00000 |
|-- |
|-- Server : info_pn0 |
|-- |
|-- Created By : informix |
|-- |
|-- Database : dw_ali |
|-- |
|-- Owner : informix |
|-- |
|-- Object Name : dw_skadetilhoer_sp |
|-- |
|-- Type : PROCEDURE |
|-- |
|-------------------------------------------------------------|
|---------------- |
|set session authorization to "informix"; |
|create procedure dw_skadetilhoer_sp(a_dw_dato like |
|skade.dw_dato, |
| a_skade_nr like |
|skade.skade_nr, |
| a_police_nr like |
|skade.police_nr, |
| a_serie_nr like |
|skade.skade_serie_nr, |
| a_skade_tp like |
|skade.skade_tp) |
| returning char(4), char(1), char(3); |
| define l_kontor_kd like kunde.kontor_kd; |
| define l_kunde_tp like kunde.kunde_tp; |
| define l_prod_kd like kunde.kontor_kd; |
| let l_kontor_kd = "HKHK"; |
| let l_kunde_tp = "X"; |
| let l_prod_kd = "ZZZ"; |
| select kontor_kd, kunde_tp, prod_kd into l_kontor_kd, |
|l_kunde_tp, l_prod_kd |
| from skade |
|where skade_nr = a_skade_nr |
| and dw_dato = (select max(b.dw_dato) from skade b |
| where b.skade_nr = a_skade_nr |
| and b.dw_dato < a_dw_dato); |
| if l_kontor_kd is null or l_kunde_tp is null then |
| select k.kontor_kd, k.kunde_tp into l_kontor_kd, |
|l_kunde_tp |
| from kunde k, police p |
| where p.police_nr = a_police_nr |
| and k.kunde_nr = p.kunde_nr |
| and a_dw_dato between k.fra_dato and k.til_dato |
| and a_dw_dato between p.fra_dato and p.til_dato; |
| end if; |
| if l_prod_kd is null then |
| select max(prod_kd) into l_prod_kd from forsikring |
| where police_nr = a_police_nr |
| and a_dw_dato between fra_dato and til_dato; |
| if l_prod_kd[1,2] = "EU" and a_serie_nr = 670 then |
| let l_prod_kd = "EUA"; |
| end if; |
| if l_prod_kd[1,2] = "EU" and a_serie_nr = 677 then |
| let l_prod_kd = "EUP"; |
| end if; |
| if l_prod_kd[1,2] = "EU" and |
| a_serie_nr != 670 and |
| a_serie_nr != 677 and |
| a_serie_nr != 688 and |
| a_serie_nr != 689 then |
| let l_prod_kd = "EUL"; |
| end if; |
| if l_prod_kd = "PAH" and a_serie_nr = 659 and |
| (a_skade_tp = "23" or |
| a_skade_tp = "29" or |
| a_skade_tp = "30" or |
| a_skade_tp = "31" or |
| a_skade_tp = "41" ) then |
| let l_prod_kd = "LNA"; |
| end if; |
| if l_prod_kd = "PAH" and a_serie_nr = 659 then |
| let l_prod_kd = "LNL"; |
| end if |
| if (l_prod_kd = "PAH" or l_prod_kd = "LNL") and |
|a_serie_nr = 600 then |
| let l_prod_kd = "LNB"; |
| end if; |
| end if; |
| return l_kontor_kd, l_kunde_tp, l_prod_kd; |
|end procedure; |
|-------------------------------------------------------------|