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.

Trigger performance

2 posts in Trigger Last posting was on 2007-04-03 16:08:52.0Z
Paul Man Posted on 2007-04-03 10:26:26.0Z
From: "Paul Man" <paulman@datasoft.ie>
Newsgroups: advantage.trigger
Subject: Trigger performance
Date: Tue, 3 Apr 2007 11:26:26 +0100
Lines: 21
Organization: DSoft
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: 82.141.233.142
Message-ID: <46122b08@solutions.advantagedatabase.com>
X-Trace: 3 Apr 2007 04:23:04 -0700, 82.141.233.142
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!82.141.233.142
Xref: solutions.advantagedatabase.com Advantage.Trigger:327
Article PK: 1136393

What's the ,most efficient method to write a trigger to perform updates
where a field or two have changed? At the moment I'm doing a join as in:

INSERT INTO SLABS( CODE, NAME, LABELNAME, QTY, COMPANY, LOCATION, SERIAL )
SELECT N.CODE, N.NAME, N.LABELTEMPLATENAME, 1, Q.COMPANY, Q.LOCATION,
(SELECT TOP 1 CODE FROM SCODE WHERE ITEMCODE = N.CODE ORDER BY ROWVER DESC)
FROM __NEW N RIGHT JOIN SQTY Q ON Q.CODE = N.CODE
LEFT JOIN __OLD O ON N.CODE= O.CODE
WHERE N.SHELFLBL = 'Y'
AND ((N.PRICE1 <> O.PRICE1) OR (N.NAME <> O.NAME))
AND NOT EXISTS (SELECT * FROM SLABS L WHERE L.CODE = N.CODE AND L.COMPANY =
Q.COMPANY AND L.LOCATION = Q.LOCATION);

There are a fewof these and it seems inefficient especially for updates
where the two specific fields Price1 and Name have not changed, where
essentially the query shouldn't be executed at all. What's the best way to
only execute the trigger queries only if the particular fields haven't
changed?


Joachim Duerr (ADS) Posted on 2007-04-03 16:08:52.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: Trigger performance
Newsgroups: Advantage.Trigger
References: <46122b08@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 130.214.79.11
Message-ID: <46126e04@solutions.advantagedatabase.com>
Date: 3 Apr 2007 09:08:52 -0700
X-Trace: 3 Apr 2007 09:08:52 -0700, 130.214.79.11
Lines: 33
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!130.214.79.11
Xref: solutions.advantagedatabase.com Advantage.Trigger:328
Article PK: 1136394


Paul Man wrote in <46122b08@solutions.advantagedatabase.com>:

> INSERT INTO SLABS( CODE, NAME, LABELNAME, QTY, COMPANY, LOCATION,
> SERIAL ) SELECT N.CODE, N.NAME, N.LABELTEMPLATENAME, 1, Q.COMPANY,
> Q.LOCATION, (SELECT TOP 1 CODE FROM SCODE WHERE ITEMCODE = N.CODE
> ORDER BY ROWVER DESC) FROM __NEW N RIGHT JOIN SQTY Q ON Q.CODE =
> N.CODE LEFT JOIN __OLD O ON N.CODE= O.CODE
> WHERE N.SHELFLBL = 'Y'
> AND ((N.PRICE1 <> O.PRICE1) OR (N.NAME <> O.NAME))
> AND NOT EXISTS (SELECT * FROM SLABS L WHERE L.CODE = N.CODE AND
> L.COMPANY = Q.COMPANY AND L.LOCATION = Q.LOCATION);
>
> There are a fewof these and it seems inefficient especially for
> updates where the two specific fields Price1 and Name have not
> changed, where essentially the query shouldn't be executed at all.
> What's the best way to only execute the trigger queries only if the
> particular fields haven't changed?

add following around your trigger code:

declare cnt integer;
cnt=(select count(*) from __new a, __old b where a.Price1=b.Price1 and
a.Name=b.Name);
if cnt>0 then
//nothing has changed
//insert your huge sql stateement here
end if;

--
Joachim Duerr
System Consultant (Advantage Database Server)
Sybase iAnywhere
advantagesupport[AT]ianywhere.com