On Wed, Apr 14, 2010 at 16:23, Daniel Albuschat <d.albuschat@...> wrote:

> I have a table with a "before update" trigger. In some special cases,
> I want to update this table without triggering that trigger.
> My guess is that "alter trigger <trigger> inactive" will make it
> inactive to all other transactions, too, right?

Correct.

> Or is it possible to make it inactive, update the table and make it
> active again, without interfering with other transactions?

AFAIK that's not possible. As soon as the trigger-deactivating
transaction is committed all other transactions won't fire that
trigger anymore. Also you shouldn't mix DDL (operations modifying your
meta data) and DML (data manipulation).

What you could do as a workaround is to add a new field to the table, like this:

ALTER TABLE mytable ADD firetrigger char(1) not null default 'T';

and adjust your trigger to only do it's work if the field
"firetrigger" has a value of 'T', like this:

ALTER TRIGGER MYTRIGGER
AS
BEGIN
IF NEW.firetrigger = 'T' THEN BEGIN
/* do work here */
END
END
^

Then when do an update you can control what the trigger does by
supplying a value for "firetrigger":

/* trigger will do nothing here: */
INSERT INTO MYTABLE intfield, charfield, firetrigger VALUES (42, 'ZALGO', 'F');

HTH,
Markus