CODEWITHSUNDEEP
triggersdeadlockfirebird
Artik
Artik

Reputation: 805

Firebird,lock inside trigger

Lately I fight with Firebird server with several clients project.I can avoid problems with deadlock in my programming enveroment but I want to do some work in triggers. Thanks to advices which I've got from StackOverflow I realy close to my goals but I can not find information about catch deadlock in trigger, wait until it unlock and continue trigger procedure. Could someone give me link or advice how to face with it?

Siple trigger definition with update or insert inside it:

CREATE TRIGGER XYZ FOR TABLE_X ACTIVE AFTER UPDATE POSITION 0 AS
begin
    UPDATE TABLE_X SET FIELD = 1 where contidion
end

How to avoid problem when the row I want to change is lock by other process?

Regards, Artik

Upvotes: 0

Views: 721

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109162

In your comments you say that you want to update the same row that the trigger fired for, in that case no deadlock can occur, as the current transaction already has the 'lock' on the row, so it is allowed to modify it again. However your approach is wrong. If you want to modify the content of the same row the trigger fired for, you should not use an AFTER UPDATE trigger, but a BEFORE UPDATE trigger and use the NEW trigger context variables to update one or more columns.

So you trigger should be something like:

CREATE TRIGGER XYZ FOR TABLE_X ACTIVE BEFORE UPDATE POSITION 0 AS
begin
    IF condition THEN
       NEW.FIELD = 1;
end

Upvotes: 1

Related Questions