Commented by: @dyemanov

It's not going to work because every DDL (including activating/disactivating indices) is actually executed on commit, so you cannot do it in the middle of transaction. That said, I suppose you could workaround that using an autonomous transaction.

Commented by: KAOUANE (kaouane)

Thank you very much;
But Why the Commit statement not work in SP like Trigger?
In trigger body, I can commit transaction like :
Insert into Vehicule (...) value (....);
Commit;
Select * from vehicule;
I think that is good thing if we can commit transaction inside PS.

Commented by: @dyemanov

Commit is not allowed in triggers either.

Commented by: KAOUANE (kaouane)

Yes it is, thank you very much.

Commented by: KAOUANE (kaouane)

I have found very good solution for this problem.
I define a context variable in usersession namespace called 'PROCEDURE' and at begin of trigger I put :

IF (rdb$get_context(''USER_SESSION'', ''PROCEDURE'')=1) THEN EXIT;

And at Begin of SP I put :

RDB$SET_CONTEXT(''USER_SESSION'',''PROCEDURE'',1);

and at last I put :

RDB$SET_CONTEXT(''USER_SESSION'',''PROCEDURE'',0);

This way permet to execute SP without execution of trigger specialy for currentuser.

Commented by: @asfernandes

I believe it's better to use USER_TRANSACTION instead of USER_SESSION in your case.

Commented by: KAOUANE (kaouane)

I believe it's same because the value of 'procedure' are value 1 just in SP transaction.