Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Triggers rules - but can I disable temporarily

5 posts in Trigger Last posting was on 2004-08-17 00:14:50.0Z
francois Posted on 2004-08-12 15:17:33.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
Subject: Triggers rules - but can I disable temporarily
Date: Thu, 12 Aug 2004 23:17:33 +0800
Lines: 50
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <411b89c5@solutions.advantagedatabase.com>
X-Trace: 12 Aug 2004 09:16:21 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:117
Article PK: 1136182

Hi
I must first say that using triggers to total flagged items in the
Bankreconciliation
table to a Totals table has saved me more than a 1000 lines of Delphi code!

The trigger (shown below fyi) does 3 things:
sum the flagged debits and credits and write it to SmTBal
sum the start_balance-debits+credits to PrelimTotal
Write the difference between PrelimTot and amount to TotalDiff

The smtBal fields are displayed on the bankrec form and after an item is
flagged the trigger kicks in. I do a Bankrec.refresh and smtBal.refresh in
the click routine and voila!!

However, I also have a routine for removing all flags (to restart flagging)
which takes ages since it fires the trigger for every row being unflagged.
It would be nice if I could just DISABLE THE TRIGGER for a second or two!

What I'm doing now is a bit risky: I drop the trigger, set all flags to
false and then reInstate the trigger. Here I've had mixed succes. if the
script fails the trigger is dropped but nothing further happens and the
trigger is lost.

Regards
Francois

==================
CREATE TRIGGER BankRecAfterUpd ON bankrec AFTER UPDATE
BEGIN
Update SmtBal set
TotDebits =(Select sum(iif( Amount<0 or Types ='C', abs(Amount),0))
from bankrec where final_flag=false and prov_flag=true),
TotCredits=(Select sum(iif( Amount>0 and Types<>'C', Amount ,0))
from bankrec where final_flag=false and prov_flag=true),
ToFlag =(Select sum(iif( Types ='C' , -Amount, Amount))
from bankrec where final_flag=false and prov_flag=false),
TrustAc1 =(Select Current_Balance from TrustAC where Account_ID=1),
Tme=Now()
where records =(select max(Records) from smtBal where balanced=false);
Update SmtBal set
PreLimTot = ifnull(FromBal,0)-ifnull(TotDebits,0)+ifnull(TotCredits,0),
TrustBal = ifnull(Amount,0)+ifNull(ToFlag,0)
where records =(select max(Records) from smtBal where balanced=false);
Update SmtBal set
TotalDiff = ifnull(PreLimTot,0)-ifnull(Amount,0)
where records =(select max(Records) from smtBal where balanced=false);
END NO MEMOS PRIORITY 1;


Francois Posted on 2004-08-16 08:23:46.0Z
From: "francois" <francois@geedee.com.au>
Newsgroups: Advantage.Trigger
References: <411b89c5@solutions.advantagedatabase.com>
Subject: I need a plan!
Date: Mon, 16 Aug 2004 16:23:46 +0800
Lines: 94
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: 202.72.180.220
Message-ID: <41206e34@solutions.advantagedatabase.com>
X-Trace: 16 Aug 2004 02:20:04 -0700, 202.72.180.220
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.180.220
Xref: solutions.advantagedatabase.com Advantage.Trigger:118
Article PK: 1136185

Hi guys

I'm in a bit of strife here! I cannot disable a trigger by dropping it even.
A sql script which takes 3 seconds without triggers now takes 12 minutes to
complete!

I've tried as a seperate script, then as part of the main script, I've
reduced its priority but it kicks in no matter what!
I can see that by looking at the actve query list inside arc32.

The upshot of this is that I run out of workareas most of the time. I have
jacked it up to 2000 (10 user system with only three users).
I'm the only user of this DD. Two other users are using a small utility on
another DD so they should not influence my ability to drop the trigger! The
script only completes if I remove the drop trigger and create trigger
sections.


Drop TRIGGER BankRecAfterUpd; /* This statement seems to be ignored*/

BEGIN TRANSACTION;
/* This usually takes 3 seconds without triggers active*/
/* but 12 minutes with triggers active*/
UPDATE SmtBal SET Balanced=True WHERE Balanced=false and TotalDiff=0;
. . .
COMMIT WORK;

CREATE TRIGGER BankRecAfterUpd ON bankrec AFTER UPDATE
BEGIN
Update SmtBal set
. . .
END NO MEMOS PRIORITY 5;

Regards
Francois

"francois" <fransh_@westnet.com.au> wrote in message
news:411b89c5@solutions.advantagedatabase.com...
> Hi
> I must first say that using triggers to total flagged items in the
> Bankreconciliation
> table to a Totals table has saved me more than a 1000 lines of Delphi
code!
>
> The trigger (shown below fyi) does 3 things:
> sum the flagged debits and credits and write it to SmTBal
> sum the start_balance-debits+credits to PrelimTotal
> Write the difference between PrelimTot and amount to TotalDiff
>
> The smtBal fields are displayed on the bankrec form and after an item is
> flagged the trigger kicks in. I do a Bankrec.refresh and smtBal.refresh in
> the click routine and voila!!
>
> However, I also have a routine for removing all flags (to restart
flagging)
> which takes ages since it fires the trigger for every row being unflagged.
> It would be nice if I could just DISABLE THE TRIGGER for a second or two!
>
> What I'm doing now is a bit risky: I drop the trigger, set all flags to
> false and then reInstate the trigger. Here I've had mixed succes. if the
> script fails the trigger is dropped but nothing further happens and the
> trigger is lost.
>
> Regards
> Francois
>
> ==================
> CREATE TRIGGER BankRecAfterUpd ON bankrec AFTER UPDATE
> BEGIN
> Update SmtBal set
> TotDebits =(Select sum(iif( Amount<0 or Types ='C', abs(Amount),0))
> from bankrec where final_flag=false and prov_flag=true),
> TotCredits=(Select sum(iif( Amount>0 and Types<>'C', Amount ,0))
> from bankrec where final_flag=false and prov_flag=true),
> ToFlag =(Select sum(iif( Types ='C' , -Amount, Amount))
> from bankrec where final_flag=false and prov_flag=false),
> TrustAc1 =(Select Current_Balance from TrustAC where Account_ID=1),
> Tme=Now()
> where records =(select max(Records) from smtBal where balanced=false);
> Update SmtBal set
> PreLimTot =
ifnull(FromBal,0)-ifnull(TotDebits,0)+ifnull(TotCredits,0),
> TrustBal = ifnull(Amount,0)+ifNull(ToFlag,0)
> where records =(select max(Records) from smtBal where balanced=false);
> Update SmtBal set
> TotalDiff = ifnull(PreLimTot,0)-ifnull(Amount,0)
> where records =(select max(Records) from smtBal where balanced=false);
> END NO MEMOS PRIORITY 1;
>
>
>


francois Posted on 2004-08-16 12:37:00.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <411b89c5@solutions.advantagedatabase.com> <41206e34@solutions.advantagedatabase.com>
Subject: Re: I need a plan!
Date: Mon, 16 Aug 2004 20:37:00 +0800
Lines: 126
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <4120aa1b@solutions.advantagedatabase.com>
X-Trace: 16 Aug 2004 06:35:39 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:119
Article PK: 1136186

Well I eventually got it down to 3 seconds using brute force:

Drop trigger
connection.isconnected:=false;
application.processmessages;
connection.isconnected:=true;
connection.execute(script);
connection.isconnected:=false;
application.processmessages;
reinstate trigger
open all tables on the datamodule as well as on this form

Any suggestions and or refinements welcome!

Regards
Francois

"francois" <francois@geedee.com.au> wrote in message
news:41206e34@solutions.advantagedatabase.com...
> Hi guys
>
> I'm in a bit of strife here! I cannot disable a trigger by dropping it
even.
> A sql script which takes 3 seconds without triggers now takes 12 minutes
to
> complete!
>
> I've tried as a seperate script, then as part of the main script, I've
> reduced its priority but it kicks in no matter what!
> I can see that by looking at the actve query list inside arc32.
>
> The upshot of this is that I run out of workareas most of the time. I
have
> jacked it up to 2000 (10 user system with only three users).
> I'm the only user of this DD. Two other users are using a small utility on
> another DD so they should not influence my ability to drop the trigger!
The
> script only completes if I remove the drop trigger and create trigger
> sections.
>
>
> Drop TRIGGER BankRecAfterUpd; /* This statement seems to be ignored*/
>
> BEGIN TRANSACTION;
> /* This usually takes 3 seconds without triggers active*/
> /* but 12 minutes with triggers active*/
> UPDATE SmtBal SET Balanced=True WHERE Balanced=false and TotalDiff=0;
> . . .
> COMMIT WORK;
>
> CREATE TRIGGER BankRecAfterUpd ON bankrec AFTER UPDATE
> BEGIN
> Update SmtBal set
> . . .
> END NO MEMOS PRIORITY 5;
>
> Regards
> Francois
>
>
> "francois" <fransh_@westnet.com.au> wrote in message
> news:411b89c5@solutions.advantagedatabase.com...
> > Hi
> > I must first say that using triggers to total flagged items in the
> > Bankreconciliation
> > table to a Totals table has saved me more than a 1000 lines of Delphi
> code!
> >
> > The trigger (shown below fyi) does 3 things:
> > sum the flagged debits and credits and write it to SmTBal
> > sum the start_balance-debits+credits to PrelimTotal
> > Write the difference between PrelimTot and amount to TotalDiff
> >
> > The smtBal fields are displayed on the bankrec form and after an item is
> > flagged the trigger kicks in. I do a Bankrec.refresh and smtBal.refresh
in
> > the click routine and voila!!
> >
> > However, I also have a routine for removing all flags (to restart
> flagging)
> > which takes ages since it fires the trigger for every row being
unflagged.
> > It would be nice if I could just DISABLE THE TRIGGER for a second or
two!
> >
> > What I'm doing now is a bit risky: I drop the trigger, set all flags to
> > false and then reInstate the trigger. Here I've had mixed succes. if the
> > script fails the trigger is dropped but nothing further happens and the
> > trigger is lost.
> >
> > Regards
> > Francois
> >
> > ==================
> > CREATE TRIGGER BankRecAfterUpd ON bankrec AFTER UPDATE
> > BEGIN
> > Update SmtBal set
> > TotDebits =(Select sum(iif( Amount<0 or Types ='C', abs(Amount),0))
> > from bankrec where final_flag=false and prov_flag=true),
> > TotCredits=(Select sum(iif( Amount>0 and Types<>'C', Amount ,0))
> > from bankrec where final_flag=false and prov_flag=true),
> > ToFlag =(Select sum(iif( Types ='C' , -Amount, Amount))
> > from bankrec where final_flag=false and prov_flag=false),
> > TrustAc1 =(Select Current_Balance from TrustAC where Account_ID=1),
> > Tme=Now()
> > where records =(select max(Records) from smtBal where balanced=false);
> > Update SmtBal set
> > PreLimTot =
> ifnull(FromBal,0)-ifnull(TotDebits,0)+ifnull(TotCredits,0),
> > TrustBal = ifnull(Amount,0)+ifNull(ToFlag,0)
> > where records =(select max(Records) from smtBal where
balanced=false);
> > Update SmtBal set
> > TotalDiff = ifnull(PreLimTot,0)-ifnull(Amount,0)
> > where records =(select max(Records) from smtBal where
balanced=false);
> > END NO MEMOS PRIORITY 1;
> >
> >
> >
>
>


Jeremy D. Mullin Posted on 2004-08-16 21:29:35.0Z
From: Jeremy D. Mullin <no@email.com>
Newsgroups: Advantage.Trigger
Subject: Re: I need a plan!
Date: Mon, 16 Aug 2004 15:29:35 -0600
Message-ID: <MPG.1b8ad52aa227f6ac989d2d@solutions.advantagedatabase.com>
References: <411b89c5@solutions.advantagedatabase.com> <41206e34@solutions.advantagedatabase.com> <4120aa1b@solutions.advantagedatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.102.102.187
X-Trace: 16 Aug 2004 15:29:44 -0700, 198.102.102.187
Lines: 69
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.187
Xref: solutions.advantagedatabase.com Advantage.Trigger:120
Article PK: 1136187

Hi Francois,

That is correct, right now the server does not refresh trigger
information until all active clients using the db have disconnected.

Temporarily disabling a trigger has been mentioned as a customer
suggestion in the past, and is on our list of possible future
enhancements.

One possible solution, although I think currently only possible through
a DLL trigger (not a script) would be to check the __new table and see
if the flag was cleared. If so, the trigger could do nothing, otherwise
it could do its work.

J.D. Mullin
Advantage R&D

In article <4120aa1b@solutions.advantagedatabase.com>,
fransh_@westnet.com.au says...

> Well I eventually got it down to 3 seconds using brute force:
>
> Drop trigger
> connection.isconnected:=false;
> application.processmessages;
> connection.isconnected:=true;
> connection.execute(script);
> connection.isconnected:=false;
> application.processmessages;
> reinstate trigger
> open all tables on the datamodule as well as on this form
>
> Any suggestions and or refinements welcome!
>
> Regards
> Francois
>
>
>
> "francois" <francois@geedee.com.au> wrote in message
> news:41206e34@solutions.advantagedatabase.com...
> > Hi guys
> >
> > I'm in a bit of strife here! I cannot disable a trigger by dropping it
> even.
> > A sql script which takes 3 seconds without triggers now takes 12 minutes
> to
> > complete!
> >
> > I've tried as a seperate script, then as part of the main script, I've
> > reduced its priority but it kicks in no matter what!
> > I can see that by looking at the actve query list inside arc32.
> >
> > The upshot of this is that I run out of workareas most of the time. I
> have
> > jacked it up to 2000 (10 user system with only three users).
> > I'm the only user of this DD. Two other users are using a small utility on
> > another DD so they should not influence my ability to drop the trigger!
> The
> > script only completes if I remove the drop trigger and create trigger
> > sections.
> >
> >
> > Drop TRIGGER BankRecAfterUpd; /* This statement seems to be ignored*/
> >
> > BEGIN TRANSACTION;
> > /* This usually takes 3 seconds without triggers active*/
> > /* but 12 minutes with triggers active*/
> > UPDATE SmtBal SET Balanced=True WHERE Balanced=false and TotalDiff=0;
> > . . .


francois Posted on 2004-08-17 00:14:50.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <411b89c5@solutions.advantagedatabase.com> <41206e34@solutions.advantagedatabase.com> <4120aa1b@solutions.advantagedatabase.com> <MPG.1b8ad52aa227f6ac989d2d@solutions.advantagedatabase.com>
Subject: Re: I need a plan!
Date: Tue, 17 Aug 2004 08:14:50 +0800
Lines: 80
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <41214dba@solutions.advantagedatabase.com>
X-Trace: 16 Aug 2004 18:13:46 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:121
Article PK: 1136188

Thanks JD, I'll just have to work around it then.
Regards
Francois

"Jeremy D. Mullin" <no@email.com> wrote in message
news:MPG.1b8ad52aa227f6ac989d2d@solutions.advantagedatabase.com...
> Hi Francois,
>
> That is correct, right now the server does not refresh trigger
> information until all active clients using the db have disconnected.
>
> Temporarily disabling a trigger has been mentioned as a customer
> suggestion in the past, and is on our list of possible future
> enhancements.
>
> One possible solution, although I think currently only possible through
> a DLL trigger (not a script) would be to check the __new table and see
> if the flag was cleared. If so, the trigger could do nothing, otherwise
> it could do its work.
>
> J.D. Mullin
> Advantage R&D
>
> In article <4120aa1b@solutions.advantagedatabase.com>,
> fransh_@westnet.com.au says...
> > Well I eventually got it down to 3 seconds using brute force:
> >
> > Drop trigger
> > connection.isconnected:=false;
> > application.processmessages;
> > connection.isconnected:=true;
> > connection.execute(script);
> > connection.isconnected:=false;
> > application.processmessages;
> > reinstate trigger
> > open all tables on the datamodule as well as on this form
> >
> > Any suggestions and or refinements welcome!
> >
> > Regards
> > Francois
> >
> >
> >
> > "francois" <francois@geedee.com.au> wrote in message
> > news:41206e34@solutions.advantagedatabase.com...
> > > Hi guys
> > >
> > > I'm in a bit of strife here! I cannot disable a trigger by dropping it
> > even.
> > > A sql script which takes 3 seconds without triggers now takes 12
minutes
> > to
> > > complete!
> > >
> > > I've tried as a seperate script, then as part of the main script, I've
> > > reduced its priority but it kicks in no matter what!
> > > I can see that by looking at the actve query list inside arc32.
> > >
> > > The upshot of this is that I run out of workareas most of the time. I
> > have
> > > jacked it up to 2000 (10 user system with only three users).
> > > I'm the only user of this DD. Two other users are using a small
utility on
> > > another DD so they should not influence my ability to drop the
trigger!
> > The
> > > script only completes if I remove the drop trigger and create trigger
> > > sections.
> > >
> > >
> > > Drop TRIGGER BankRecAfterUpd; /* This statement seems to be ignored*/
> > >
> > > BEGIN TRANSACTION;
> > > /* This usually takes 3 seconds without triggers active*/
> > > /* but 12 minutes with triggers active*/
> > > UPDATE SmtBal SET Balanced=True WHERE Balanced=false and
TotalDiff=0;
> > > . . .