Deferring Triggers, using sub-transactions.

The standard way of managing it is to define AFTER triggers, and use transactions. Those triggers only raise their action at the COMMIT time, when all your update queries are done, but before locks applied for the query are released. You can safely design a transaction in all your multiquery stored procedures, even if these procedures must be called from another procedure or transaction. For that, you need to manage embedded transactions, a function which Informix does not support by default but that you can emulate with a few (fast) procedure calls: CREATE TABLE sessions( sessionid INT NOT NULL, trancount INT NOT NULL) LOCK MODE ROW; Then you need the current session id; in Online 7.2, just use DBINFO("sessionid"). In Online 7.1, you can use the following procedure orcode part: CREATE PROCEDURE sessionid() RETURNING INT; DEFINE id INT; SELECT sessionid INTO id FROM sysmaster:syssession WHERE mod(state, 64) >= 32; RETURN id; END PROCEDURE; This procedure works at least on all mono-processor systems (the above query detects the session which is currently running, by the bit 5 (value 32) being set in the state field in the syssession table). You can add the following restriction to the WHERE clause in the query above: AND username=USER. Then you can create small and efficient procedures to replace the standard BEGIN WORK, COMMIT WORK, ROLLBACK WORK statements, emulating the embedded subtransactions of MS or Sybase SQL-Servers: 1) Replace BEGIN WORK by a call to begintran() defined like this: CREATE PROCEDURE begintran() DEFINE v_id INT; SELECT v_id = sessionid(); { or query above, or DBINFO("sessionid") is 7.2 } IF NOT EXISTS(SELECT 1 FROM sessions WHERE sessionid=id) THEN BEGIN WORK; INSERT INTO sessions VALUES(v_id, 1); ELSE UPDATE sessions SET trancount=trancount+1 WHERE sessionid=id END IF;END PROCEDURE; 2) Replace COMMIT WORK by a call to committran() defined as: CREATE PROCEDURE committran() DEFINE v_id INT; DEFINE v_trancount INT; SELECT v_id = sessionid(); { or query above, or DBINFO("sessionid") is 7.2 } IF EXISTS(SELECT 1 FROM sessions WHERE sessionid=v_id) THEN SELECT trancount INTO v_trancount FROM sessions WHERE sessionid=v_id; IF v_trancount > 0 THEN UPDATE sessions SET trancount = v_trancount - 1; ELSE DELETE FROM sessions WHERE sessionid=v_id; COMMIT WORK; END IF; END IF; END PROCEDURE; You may omit the IF EXISTS if you are sure of always using the appropriate sets of calls, it justs avoids an exception when v_trancount is not defined by the SELECT after it. 3) Replace ROLLBACK WORK by a call to rollbacktran() defined as: CREATE PROCEDURE rollbacktran() DEFINE v_id INT; SELECT v_id = sessionid(); { or query above, or DBINFO("sessionid") is 7.2 } IF EXISTS(SELECT 1 FROM sessions WHERE sessionid=v_id) THEN ROLLBACK WORK; END IF; END PROCEDURE; Note that here, ROLLBACK WORK automatically deletes the inserted row in sessions table. No running session except the current one should see your line in the session table be cause it should never be committed. Out of any runnning transaction, this table should remain empty. Moreover, in case of network failure, an automatic ROLLBACK will occur which will avoid this table of always growing. At shutdown or restart, this table will always be empty. The ROW locking mode is important on the sessions table to avoid inter-sessions locking in the sessions table, because many running transactions can fit in a single page of this small table. You can alternately create a specific exception handler in your procedures to know if a transaction is already running: just call BEGIN WORK, and a exception will raise if one is currently active, which can set a variable before resuming: DEFINE SQLCODE INT; LET SQLCODE=0; BEGIN WHEN EXCEPTION SET SQLCODE END EXCEPTION; BEGIN WORK; END IF SQLCODE=0 THEN ROLLBACK WORK; { OK we are not in transaction for now } { set a flag or simply use the SQLCODE=0 status and continue } ELSE { we are already in transaction } { reset the flag and continue } END IF; { here we can use the flag value to do the appropriate job } However, counting uses of subtransactions is the only way to know WHEN to COMMIT the main transaction (i.e. to know if the current subtransaction IS the main transaction). This is useful when using recursive procedures (mainly used to parse data organised as trees, when no other algorithm using looping and sorting a temporary worktable can apply to solve the problem). Further, this method fully emulates the @@trancount function of Sybase and MS SQL-Server. This method is so simple to write and efficient that we can wonder why Informix does not provide it built-in the server, instead of raising exceptions when we use BEGIN WORK unappropriately. Subtransactions have been found as a good method of always using safe transactions in all procedures, improving their reuseability, and avoiding development of large, difficult to manage stored procedures. David Mascorro <mascorrd@usaa.com> a 'crit dans l'article <01bc96da$09d3e590$bf0a1f0a@l6024433>... > I have a table that has an update trigger defined. There are certain times > when I need to run an update on this table without the trigger triggering. > Is there a command I can execute that will temporarily turn off the trigger > so I can run an update and then turn it back on afterwords? > > Thanks, > Dave Mascorro > mascorrd@usaa.com >