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.

Help with writing a SQL trigger

2 posts in Trigger Last posting was on 2005-06-29 08:19:16.0Z
Farren Constable Posted on 2005-06-29 01:33:46.0Z
Date: Tue, 28 Jun 2005 20:33:46 -0500
X-Newsreader: Groupwise 6.5
From: "Farren Constable" <farren@aibonline.org>
Subject: Help with writing a SQL trigger
Newsgroups: Advantage.Trigger
NNTP-Posting-Host: 64.80.234.195
Message-ID: <42c1fc7c@solutions.advantagedatabase.com>
X-Trace: 28 Jun 2005 19:42:20 -0700, 64.80.234.195
Lines: 10
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!64.80.234.195
Xref: solutions.advantagedatabase.com Advantage.Trigger:167
Article PK: 1136232

I claim 'newbie' status, so sorry for posting such a question....

Two tables, A & B. A consists of records that have been copied from B. When records in B change, I need a trigger that updates all the records in A that were originally copied from B. They have a common key: "ID" All the examples I find are dong an Insert with the new data, not an Update.

My Question is this: how do I get the new field values of the record that caused the trigger in table B in order to use those as part of the SET statement and WHERE statement in my Update of table A? I know about __old and __new, but I don't seem to be able to simply do "Where A.ID = __new.ID" or "Set A.Name = __new.Name"

Thanks for the patience and help!

-Farren


Joachim Duerr (ADS Support) Posted on 2005-06-29 08:19:16.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: Help with writing a SQL trigger
Newsgroups: Advantage.Trigger
References: <42c1fc7c@solutions.advantagedatabase.com>
Organization: Extended Systems GmbH
User-Agent: XanaNews/1.17.4.1
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: 195.2.185.25
Message-ID: <42c24b74@solutions.advantagedatabase.com>
Date: 29 Jun 2005 01:19:16 -0700
X-Trace: 29 Jun 2005 01:19:16 -0700, 195.2.185.25
Lines: 37
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Trigger:168
Article PK: 1136234


Farren Constable wrote in <42c1fc7c@solutions.advantagedatabase.com> :

> Two tables, A & B. A consists of records that have been copied from
> B. When records in B change, I need a trigger that updates all the
> records in A that were originally copied from B. They have a common
> key: "ID" All the examples I find are dong an Insert with the new
> data, not an Update.

if you don't use autoinc, the easiest way would be to delete the
records in A and then add them again:

create trigger trig_ins_b
on B
after update
begin
delete from a where id = (select id from __new);
insert into a select * from __new;
end;

otherwise you need to change all the fields in A manually:

create trigger trig_ins_b
on B
after update
begin
update a set
field1=(select field1 from __new),
field2=(select field2 from __new),
...
where id=(select id from __new);
end;


--
Joachim Duerr
EMEA Lead ADS Support, Extended Systems GmbH, Germany
advantage[AT]extendsys.de