trigger on insert: updating one value of the same table

[Gentian Hila]

I have a program that is closed to us. It inserts a new row, every
column of the table.

I want one to update the value of one column on the inserted row.

I tried a INSERT on trigger but since the column is included on the
insert it gives me the error:

"table or column matches object referenced in triggering statement".

Is there a way that I can achieve this through a trigger?

That would be extremely helpful.

Thank you,

[dba...@gmail.com]

In article <mailman.372.12604775...@iiug.org>, Gentian Hila
says...

can you put the sql you used to accomplish what you want.

I know Informix supports updating the same col of the table
where the trigger is. The trick is to use stored procedure
with into clause.

Sample code pasted below (no testing, I am giving it off my hat)

CREATE TRIGGER TEST
INSERT ON YOUR_TABLE
referencing new as n old as o
foreach row
execute procedure yourproc() into n.yourcol

Here yourcol is the column you would want to update.
You have to use a sproc for this which will throw back
the value you want to update with. Sproc will make it
slow, but have no choice.

Once again the above code is untested and I am extrapolating
what I did with UPDATE TRIGGER.

Please post the result of your testing.

[Art Kagel]

Insert a row from the INSERT TRIGGER into another staging table containing the keys to the inserted row.  Then a separate task can run or loop every N seconds that extracts new rows from the staging table, performs the update, and deletes the staging row.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

On Thu, Dec 10, 2009 at 3:38 PM, Gentian Hila <genti...@gmail.com> wrote:

I have a program that is closed to us. It inserts a new row, every
column of the table.

I want one to update the value of one column on the inserted row.

I tried a INSERT on trigger but since the column is included on the
insert it gives me the error:

"table or column matches object referenced in triggering statement".

Is there a way that I can achieve this through a trigger?

That would be extremely helpful.

Thank you,

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Superboer]

Dono if it is legal... should read the manual better however:

change your table to be a view;

say your table was called x:
then rename x to xtab....

ex:

create table xtab ( a int, b char(10), c int );

create view x as select * from xtab;
CREATE PROCEDURE tessie (myaval INT, mybval char(10) , mycval int )
insert into xtab values ( myaval, mybval, mycval + myaval);
END PROCEDURE;

CREATE TRIGGER insertxv
INSTEAD OF INSERT ON x
REFERENCING NEW AS n
FOR EACH ROW
(EXECUTE PROCEDURE tessie(n.a , n.b , n.c ));

insert into x values ( 1,"c=1+6=7",6);

select * from xtab;
select * from x;

Superboer.

dba6...@gmail.com schreef:

[Gentian Hila]

I did a test on this as the whole SQL is a long thing and has other
parts in there that would simply make it more confusing.

1) Create a test table

create table test1
(cust_num CHAR(10),
desc CHAR(30),
val INTEGER)

2) Created a procedure that returns an integer

create procedure p_test1()
RETURNING INTEGER
return 100;
END PROCEDURE

3) Tried to create a trigger just like you suggest:

CREATE TRIGGER t_test1
INSERT ON test1
referencing new as new
for each row
(execute procedure p_test1() INTO new.val);

but it gives me an error:

Incorrect use of old or new values correlation name inside trigger. So
I guess this can be done on update but not on insert

Thanks for your suggestion.

[Gentian Hila]

On Thu, Dec 10, 2009 at 5:46 PM, Art Kagel <art....@gmail.com> wrote:
> Insert a row from the INSERT TRIGGER into another staging table containing
> the keys to the inserted row.  Then a separate task can run or loop every N
> seconds that extracts new rows from the staging table, performs the update,
> and deletes the staging row.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly.  Neither do
> those opinions reflect those of other individuals affiliated with any entity
> with which I am affiliated nor those of the entities themselves.
>
>
>
> On Thu, Dec 10, 2009 at 3:38 PM, Gentian Hila <genti...@gmail.com> wrote:
>>

>> I have a program that is closed to us. It inserts a new row, every
>> column of the table.
>>
>> I want one to update the value of one column on the inserted row.
>>
>> I tried a INSERT on trigger but since the column is included on the
>> insert it gives me the error:
>>
>> "table or column matches object referenced in triggering statement".
>>
>> Is there a way that I can achieve this through a trigger?
>>
>> That would be extremely helpful.
>>
>> Thank you,

>> _______________________________________________
>> Informix-list mailing list
>> Inform...@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>
>

Thank you,

This should work. The only thing I am worried is that there are many
insertions and the value should change not to long after the insertion
so I guess I will have to run the procedure every 2 seconds. It will
be extra lookup when there are no insertions at all.

[Gentian Hila]

I have IDS 9.40 ( we will upgrade soon to 11.50).

However, here it is what documentation on ibm site says for v.10:

If the trigger has an INSERT event, the trigger action can be an
UPDATE statement that references a column in the triggering table, but
this column cannot be a column for which a value was supplied by the
trigger event.
If the trigger has an INSERT event, and the trigger action updates the
triggering table, the columns in both statements must be mutually
exclusive. For example, assume that the triggering statement inserts
values for columns cola and colb of table tab1:

INSERT INTO tab1 (cola, colb) VALUES (1,10)Now consider the following
trigger actions. The first UPDATE is valid, but the second one is not,
because it updates column colb even though the trigger event already
supplied a value for column colb:

UPDATE tab1 SET colc=100; --OK
UPDATE tab1 SET colb=100; --INVALID

If this is true for v.9.40 which I think it is, then Art's suggestion
will be probably the best way to go.

[Gentian Hila]

I am sorry, I do not understand this part:

> insert into x values ( 1,"c=1+6=7",6);

Can you explain a little bit more please?

Thank you,

[Art Kagel]

Have an external program that opens a cursor on the staging table, processes whatever it finds then loops and starts again.  If the cursor finds no work to do, sleep for a reasonable time and try again.  That way the worst case delay is during quiet periods and is the sleep time, normally everything gets processed very quickly.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

On Fri, Dec 11, 2009 at 11:09 AM, Gentian Hila <genti...@gmail.com> wrote:

On Thu, Dec 10, 2009 at 5:46 PM, Art Kagel <art....@gmail.com> wrote:
> Insert a row from the INSERT TRIGGER into another staging table containing
> the keys to the inserted row.  Then a separate task can run or loop every N
> seconds that extracts new rows from the staging table, performs the update,
> and deletes the staging row.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly.  Neither do
> those opinions reflect those of other individuals affiliated with any entity
> with which I am affiliated nor those of the entities themselves.
>
>
>

> On Thu, Dec 10, 2009 at 3:38 PM, Gentian Hila <genti...@gmail.com> wrote:
>>

>> I have a program that is closed to us. It inserts a new row, every
>> column of the table.
>>
>> I want one to update the value of one column on the inserted row.
>>
>> I tried a INSERT on trigger but since the column is included on the
>> insert it gives me the error:
>>
>> "table or column matches object referenced in triggering statement".
>>
>> Is there a way that I can achieve this through a trigger?
>>
>> That would be extremely helpful.
>>
>> Thank you,

>> _______________________________________________
>> Informix-list mailing list
>> Inform...@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>
>

Thank you,

This should work. The only thing I am worried is that there are many
insertions and the value should change not to long after the insertion
so I guess I will have to run the procedure every 2 seconds. It will
be extra lookup when there are no insertions at all.

[Superboer]

Hello

insert into x values ( 1,"c=1+6=7",6);

i insert into the view and i get

a b c
1,"c=1+6=7",7
instead of
a b c
1,"c=1+6=7",6

means that an insert can be bend so that a value for a column can be
updated before it is inserted and
if i understand your question properly that is what you were after.
So col c is updated from 6 to 7 during the insert.

again do not know if it is legal to change the table where the view is
based on, could not find a thing rejecting this
in the manual; maybe Madison or Jonathan can put some comments...

Superboer.

BTW just run the sql.....

On 11 dec, 17:23, Gentian Hila <genti.t...@gmail.com> wrote:
> I am sorry, I do not understand this part:
>
> > insert into  x values ( 1,"c=1+6=7",6);
>
> Can you explain a little bit more please?
>
> Thank you,
>

> On Fri, Dec 11, 2009 at 5:38 AM, Superboer <superbo...@t-online.de> wrote:
> > Dono if it is legal... should read the manual better however:
>
> > change your table to be a view;
>
> > say your table was called x:
> > then rename x to xtab....
>
> > ex:
>
> > create table xtab ( a int, b char(10), c int  );
>
> > create view x as select * from xtab;
> > CREATE PROCEDURE tessie (myaval INT, mybval char(10) , mycval int )
> >  insert into xtab   values ( myaval, mybval, mycval + myaval);
> > END PROCEDURE;
>
> > CREATE TRIGGER insertxv
> >   INSTEAD OF INSERT ON x
> >      REFERENCING NEW AS n
> >   FOR EACH ROW
> >      (EXECUTE PROCEDURE tessie(n.a , n.b  , n.c ));
>
> > insert into  x values ( 1,"c=1+6=7",6);
>
> > select * from xtab;
> > select * from x;
>
> > Superboer.
>
> > dba6...@gmail.com schreef:

> >> In article <mailman.372.1260477541.6236.informix-l...@iiug.org>, Gentian Hila

> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list

[SaltTan]

On 11 дек, 18:57, Gentian Hila <genti.t...@gmail.com> wrote:
> I did a test on this as the whole SQL is a long thing and has other
> parts in there that would simply make it more confusing.
>
> 1) Create a test table
>
> create table test1
> (cust_num CHAR(10),
> desc CHAR(30),
> val INTEGER)
>
> 2) Created a procedure that returns an integer
>
> create procedure p_test1()
> RETURNING INTEGER
> return 100;
> END PROCEDURE
>
> 3) Tried to create a trigger just like you suggest:
>
> CREATE TRIGGER t_test1
> INSERT ON test1
> referencing new as new
> for each row
> (execute procedure p_test1() INTO new.val);
>
> but it gives me an error:
>
> Incorrect use of old or new values correlation name inside trigger. So
> I guess this can be done on update but not on insert
>

You should not use correlation names in this case.
Works for me in insert trigger.

CREATE TRIGGER t_test1
INSERT ON test1
referencing new as new
for each row

(execute procedure p_test1() INTO val);