Re: Migrate triggers from Oracle to Informix
In Informix you need separate INSERT and UPDATE triggers. To ease
maintenance you can place all of the logic into a stored procedure and
call that procedure from both the INSERT and UPDATE trigger.
Art S. Kagel
pablof.herrero@srrpa.es wrote:
>
> Hi everybody:
> I want to migrate a trigger from Oracle to Informix and I don´t find
> the right translation.
> CREATE TRIGGER TRG1> BEFORE INSERT OR UPDATE ON tabla FOR EACH ROW
> BEGIN
> :new.FEC := SUBSTR(:new.FECHA,1,4)||' - '||:new.NUM;
> IF (:new.NSP1 = '' or :new.NSP1 is null) and
> (:new.NIFSP1 = '' or :new.NIFS1 is null)
> THEN
> :new.NSP1 := :new.NREM;
> :new.NIFSP1 := :new.NIFREM;
> END IF;
> END;
> Thanks in advance
>
> --
> Pablo F Herrero Fernandez
> Gijon
> Asturias (Spain)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Hello Art:
First of all thank you for answering me. My hesitate arises from the
words BEFORE and FOR EACH ROW in INSERT events. I could make a trigger
with the same meaning for update events and using "INTO" clause with
stored procedures. But I don't know how expressing with INSERT. I need
modify the values which must be stored in the same table that I'm
triggering. Can I do it?
Thank you.
In article <38456BD1.1410894C@bloomberg.net>,
kagel@bloomberg.net wrote:
> In Informix you need separate INSERT and UPDATE triggers. To ease
> maintenance you can place all of the logic into a stored procedure
and
> call that procedure from both the INSERT and UPDATE trigger.
>
> Art S. Kagel
>
> pablof.herrero@srrpa.es wrote:
> >
> > Hi everybody:
> > I want to migrate a trigger from Oracle to Informix and I don't find
> > the right translation.
> > CREATE TRIGGER TRG1> > BEFORE INSERT OR UPDATE ON tabla FOR EACH ROW
> > BEGIN
> > :new.FEC := SUBSTR(:new.FECHA,1,4)||' - '||:new.NUM;
> > IF (:new.NSP1 = '' or :new.NSP1 is null) and
> > (:new.NIFSP1 = '' or :new.NIFS1 is null)
> > THEN
> > :new.NSP1 := :new.NREM;
> > :new.NIFSP1 := :new.NIFREM;
> > END IF;
> > END;
> > Thanks in advance
> >
> > --
> > Pablo F Herrero Fernandez
> > Gijon
> > Asturias (Spain)
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
--
Pablo F Herrero Fernandez
Gijon
Asturias (Spain)
Sent via Deja.com http://www.deja.com/
Before you buy.
pablof.herrero@srrpa.es wrote:
>
> Hello Art:
> First of all thank you for answering me. My hesitate arises from the
> words BEFORE and FOR EACH ROW in INSERT events. I could make a trigger
> with the same meaning for update events and using "INTO" clause with
> stored procedures. But I don´t know how expressing with INSERT. I need
> modify the values which must be stored in the same table that I'm
> triggering. Can I do it?
> Thank you.
OK Assuming you have IDS version 7.30 or later (ver 7.[12]x will not allow
updating the same table in the trigger), he INSERT TRIGGER would look
like:
CREATE TRIGGER i_trg1 INSERT ON tablaREFERENCING new AS nueva
FOR EACH ROW
(UPDATE tabla
SET fec = substr( neuva.fecha,1,4)||' - '||nueva.num),
WHEN ((nueva.nsp1 = '' OR nueva.nsp1 IS NULL) AND
(nueva.nifsp1 = '' OR nueva.nifsp1 IS NULL))
(UPDATE tabla
SET (nsp1, nifsp1) = (nueva.nrem,nueva.nufrem));
This will work as long as the columns being updated are NOT included in
the column list named in the INSERT statement. Because of the WHEN clause
the if either the nsp1 or NIFSP1 column is not included, and therefore is
NULL, then NEITHER can be included or the insert will fail with a -747
error: Table or column matches object referenced in triggering statement.
Similarly the fec column cannot EVER be mentioned, either explicitely or
implicitely, in the column list (remember no column list is equivalent of
all columns being listed).
Therefore you may need to make the WHEN clauses more complex to avoid such
errors. Also note that some of what you seem to want to do can be done
with a DEFAULT constraint which executes faster than a trigger.
Art S. Kagel
> In article <38456BD1.1410894C@bloomberg.net>,
> kagel@bloomberg.net wrote:
> > In Informix you need separate INSERT and UPDATE triggers. To ease
> > maintenance you can place all of the logic into a stored procedure
> and
> > call that procedure from both the INSERT and UPDATE trigger.
> >
> > Art S. Kagel
> >
> > pablof.herrero@srrpa.es wrote:
> > >
> > > Hi everybody:
> > > I want to migrate a trigger from Oracle to Informix and I don´t find
> > > the right translation.
> > > CREATE TRIGGER TRG1> > > BEFORE INSERT OR UPDATE ON tabla FOR EACH ROW
> > > BEGIN
> > > :new.FEC := SUBSTR(:new.FECHA,1,4)||' - '||:new.NUM;
> > > IF (:new.NSP1 = '' or :new.NSP1 is null) and
> > > (:new.NIFSP1 = '' or :new.NIFS1 is null)
> > > THEN
> > > :new.NSP1 := :new.NREM;
> > > :new.NIFSP1 := :new.NIFREM;
> > > END IF;
> > > END;
> > > Thanks in advance
> > >
> > > --
> > > Pablo F Herrero Fernandez
> > > Gijon
> > > Asturias (Spain)
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
>
> --
> Pablo F Herrero Fernandez
> Gijon
> Asturias (Spain)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.