Tim, Roy.

The problem is that trigger use transactions then I can use another one by code.

Any other way to improve the process?

Thanks

Francisco

>The problem is that trigger use transactions then I can use another one by code.

I wondered about that but I thought if there was a clash it would result in a deadlock rather than an error.

>Any other way to improve the process?

Just skimming your trigger there's a lot going on and its going to take time to process for each record. Getting the trigger more effective will probably be the best way of speeding the whole process up. There are possibly some bits that can be changed eg

 PREPARE stmt FROM 'SELECT * FROM ASIENTOS WHERE ASIENTO=?';
 OPEN TempCursor USING OLDROW.ASIENTO;
 IF ROWCOUNT(TempCursor)>0 THEN
   DELETE FROM TempCursor;
 END IF;

might well work as

EXECUTE IMMEDIATE 'DELETE FROM ASIENTOS WHERE ASIENTO = '+CAST(OLDROW.ASIENTO AS VARCHAR);

and be faster.

I don't know how things work in the engine so Tim will have to comment on this but it might be more efficient to have multiple cursors being prepared rather than reusing the same cursor with different sql code.

Again I don't know but its worth seeing if a CASE (or even IF THEN ELSEIF) rather than a sequence of IF statements would be faster (as well as being more readable)

I think that you can speed stuff up but whatever you do the amount of processing involved it will always take time.

The only other thing I can suggest is if the UI permits then move the operation into a thread.

Roy Lambert [Team Elevate]

Roy.

Thank you for your suggestions.

My knowledge of triggers and SQL are still low and surely my triggers can be optimized,
like my english :P

I'll revise my triggers as you can say to try improve it.

Thank you and regards

Roy.

I'm trying the instruction you say

EXECUTE IMMEDIATE 'DELETE FROM ASIENTOS WHERE ASIENTO = '+CAST(OLDROW.ASIENTO AS VARCHAR);

but don't works because ASIENTO is type VARCHAR and must be between quotes and the
instruction

EXECUTE IMMEDIATE 'DELETE FROM ASIENTOS WHERE ASIENTO = "'+CAST(OLDROW.ASIENTO AS
VARCHAR)+'"';

don't works

Are there any instruction in SQL like QuotedStr of Delphi?

Thanks

Francisco,

<< Are there any instruction in SQL like QuotedStr of Delphi? >>

Yep, QUOTEDSTR():

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=17&topic=369



--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< I don't know how things work in the engine so Tim will have to comment on
this but it might be more efficient to have multiple cursors being prepared
rather than reusing the same cursor with different sql code. >>

Yes, you are correct, it will be more efficient to have pre-prepared queries
that just use different parameters.

--
Tim Young
Elevate Software
www.elevatesoft.com

Francisco

>My knowledge of triggers and SQL are still low and surely my triggers can be optimized,
>like my english :P

You English in the posts isn't to bad, its the gibberish looking (to an English only speaker) in the SQL that's awkward to follow. My brain keeps trying to make English of words like Nivel and ASIENTO and naturally failing

Roy Lambert [Team Elevate]

Francisco

>I'm trying the instruction you say
>
>EXECUTE IMMEDIATE 'DELETE FROM ASIENTOS WHERE ASIENTO = '+CAST(OLDROW.ASIENTO AS VARCHAR);
>
>but don't works because ASIENTO is type VARCHAR and must be between quotes and the
>instruction

Sorry I just guessed it was integer

>EXECUTE IMMEDIATE 'DELETE FROM ASIENTOS WHERE ASIENTO = "'+CAST(OLDROW.ASIENTO AS
>VARCHAR)+'"';
>
>don't works
>
>Are there any instruction in SQL like QuotedStr of Delphi?

It depends on which version you have, Have a look in the index in the OLH for EDBManager and if it finds QUOTEDSTR you're in luck otherwise you have to enter the necessary quotes yourself..

Roy Lambert [Team Elevate]

Tim

>Yes, you are correct, it will be more efficient to have pre-prepared queries
>that just use different parameters.

I know you HATE these sort of questions but any rough idea as to a %

Roy Lambert

Roy,

<< I know you HATE these sort of questions but any rough idea as to a % >>

There's no possible way that I could answer that.  It depends upon what the
original queries look like, and what the pre-prepared queries look like, and
then what is done with the pre-prepared queries.

--
Tim Young
Elevate Software
www.elevatesoft.com