SELECT FOR UPDATE and triggers

Posted in 2017

FLORIAN APOLLONER — Fri, 25 Aug 2017 18:39:12 — source: IIUG Forums & Mailing Lists

Hi, I am having a problem with triggers which I do not understand: * I have two tables, lets call them a & b. * Table b has a trigger which (for simplicity calls "UPDATE a SET some_field=42") on UPDATES/INSERTS (you name it). * Now I call "SELECT * FROM a FOR UPDATE" which acquires an U and IX lock -- so far so good * Now I call (from the same database connection/session, just via another cursor) an arbitrary statement which triggers the trigger on b. The result is that I get an error because informix cannot lock the row in a. This is somewhat expected but I thought/hoped that locks inside one connection would be "shared". After all calling "UPDATE a SET WHERE CURRENT OF " works and also uses a second cursor. Is there any way to make the trigger "inherit" the locks? Or am I missing something else? I hope the question is clear enough, if not please say so and I'll provide concrete test tables/statements. Thanks, Florian