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

4 posts in General Discussion Last posting was on 2007-10-25 20:56:18.0Z
Geoffrey Chambers Posted on 2007-10-25 16:15:32.0Z
Sender: 74f3.4720bc5d.1804289383@sybase.com
From: Geoffrey Chambers
Newsgroups: ianywhere.public.general
Subject: Triggers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4720c124.7564.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Oct 2007 09:15:32 -0700
X-Trace: forums-1-dub 1193328932 10.22.241.41 (25 Oct 2007 09:15:32 -0700)
X-Original-Trace: 25 Oct 2007 09:15:32 -0700, 10.22.241.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6482
Article PK: 2915

I'm new to the idea of triggers, but would like to know if
I'm headed in the right direction. I have a 2 tables, when a
new row is added to subcontractors, I would like to update
fields in the jobcost table based on the row added in the
first table. I tried to create a trigger, but I'm a little
confused on how I can restrict the update on the second
table. I created the following trigger but really don't see
how it would know the values of job,subjob,costcode,costtype
of the newly created row for subcontractors that was just
added.

ALTER TRIGGER "AddJCVendorId" AFTER INSERT
ORDER 1 ON "DBA"."Subcontractors"
/* REFERENCING NEW AS new_name */
FOR EACH STATEMENT /* WHEN( search_condition ) */
BEGIN
update jobcost set jobcost.vendorid =
subcontractors.vendorid,jobcost.contract_amount =
subcontractors.sc,jobcost.contract_no =
subcontractors.contract_no
where jobcost.job = subcontractors.job and jobcost.subjob
= subcontractors.subjob and jobcost.costcode =
subcontractors.costcode and jobcost.costtype =
subcontractors.costtype
END


Breck Carter [Team iAnywhere] Posted on 2007-10-25 17:14:49.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Triggers
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <1dj1i35307lcer46mn05ajd9747vv8oc5o@4ax.com>
References: <4720c124.7564.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 25 Oct 2007 10:14:49 -0700
X-Trace: forums-1-dub 1193332489 64.7.134.118 (25 Oct 2007 10:14:49 -0700)
X-Original-Trace: 25 Oct 2007 10:14:49 -0700, bcarter.sentex.ca
Lines: 59
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6483
Article PK: 4777

Short answer: buy my book :)

Long answer: you may be happier with a row-level trigger since then
you can treat REFERENCING NEW AS name as a single "record" with dot
notation.

Caveat Emptor: I have not tested the following syntax:

ALTER TRIGGER "AddJCVendorId" BEFORE INSERT
ORDER 1 ON "DBA"."Subcontractors"
REFERENCING NEW AS newsub
FOR EACH ROW
BEGIN
update jobcost set jobcost.vendorid =
newsub.vendorid,jobcost.contract_amount =
newsub.sc,jobcost.contract_no =
newsub.contract_no
where jobcost.job = newsub.job and jobcost.subjob
= newsub.subjob and jobcost.costcode =
newsub.costcode and jobcost.costtype =
newsub.costtype;
END;

Breck

On 25 Oct 2007 09:15:32 -0700, Geoffrey Chambers wrote:

>I'm new to the idea of triggers, but would like to know if
>I'm headed in the right direction. I have a 2 tables, when a
>new row is added to subcontractors, I would like to update
>fields in the jobcost table based on the row added in the
>first table. I tried to create a trigger, but I'm a little
>confused on how I can restrict the update on the second
>table. I created the following trigger but really don't see
>how it would know the values of job,subjob,costcode,costtype
>of the newly created row for subcontractors that was just
>added.
>
>ALTER TRIGGER "AddJCVendorId" AFTER INSERT
>ORDER 1 ON "DBA"."Subcontractors"
>/* REFERENCING NEW AS new_name */
>FOR EACH STATEMENT /* WHEN( search_condition ) */
>BEGIN
> update jobcost set jobcost.vendorid =
>subcontractors.vendorid,jobcost.contract_amount =
>subcontractors.sc,jobcost.contract_no =
>subcontractors.contract_no
> where jobcost.job = subcontractors.job and jobcost.subjob
>= subcontractors.subjob and jobcost.costcode =
>subcontractors.costcode and jobcost.costtype =
>subcontractors.costtype
>END

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Geoffrey Chambers Posted on 2007-10-25 20:52:27.0Z
Sender: 74f3.4720bc5d.1804289383@sybase.com
From: Geoffrey Chambers
Newsgroups: ianywhere.public.general
Subject: Re: Triggers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4721020b.7f49.1681692777@sybase.com>
References: <1dj1i35307lcer46mn05ajd9747vv8oc5o@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Oct 2007 13:52:27 -0700
X-Trace: forums-1-dub 1193345547 10.22.241.41 (25 Oct 2007 13:52:27 -0700)
X-Original-Trace: 25 Oct 2007 13:52:27 -0700, 10.22.241.41
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6487
Article PK: 4780

I have your book, just didn't have it with me. I'll have to
re-read it when I get home, but got it to work. Thanks.

> Short answer: buy my book :)
>
> Long answer: you may be happier with a row-level trigger
> since then you can treat REFERENCING NEW AS name as a
> single "record" with dot notation.
>
> Caveat Emptor: I have not tested the following syntax:
>
> ALTER TRIGGER "AddJCVendorId" BEFORE INSERT
> ORDER 1 ON "DBA"."Subcontractors"
> REFERENCING NEW AS newsub
> FOR EACH ROW
> BEGIN
> update jobcost set jobcost.vendorid =
> newsub.vendorid,jobcost.contract_amount =
> newsub.sc,jobcost.contract_no =
> newsub.contract_no
> where jobcost.job = newsub.job and jobcost.subjob
> = newsub.subjob and jobcost.costcode =
> newsub.costcode and jobcost.costtype =
> newsub.costtype;
> END;
>
> Breck
>
> On 25 Oct 2007 09:15:32 -0700, Geoffrey Chambers wrote:
>
> >I'm new to the idea of triggers, but would like to know
> if >I'm headed in the right direction. I have a 2 tables,
> when a >new row is added to subcontractors, I would like
> to update >fields in the jobcost table based on the row
> added in the >first table. I tried to create a trigger,
> but I'm a little >confused on how I can restrict the
> update on the second >table. I created the following
> trigger but really don't see >how it would know the values
> of job,subjob,costcode,costtype >of the newly created row
> for subcontractors that was just >added.
> >
> >ALTER TRIGGER "AddJCVendorId" AFTER INSERT
> >ORDER 1 ON "DBA"."Subcontractors"
> >/* REFERENCING NEW AS new_name */
> >FOR EACH STATEMENT /* WHEN( search_condition ) */
> >BEGIN
> > update jobcost set jobcost.vendorid =
> >subcontractors.vendorid,jobcost.contract_amount =
> >subcontractors.sc,jobcost.contract_no =
> >subcontractors.contract_no
> > where jobcost.job = subcontractors.job and
> jobcost.subjob >= subcontractors.subjob and
> jobcost.costcode = >subcontractors.costcode and
> jobcost.costtype = >subcontractors.costtype
> >END
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
>
http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Geoffrey Chambers Posted on 2007-10-25 20:56:18.0Z
Sender: 7f52.472102c1.1804289383@sybase.com
From: Geoffrey Chambers
Newsgroups: ianywhere.public.general
Subject: Re: Triggers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <472102f2.7f56.1681692777@sybase.com>
References: <1dj1i35307lcer46mn05ajd9747vv8oc5o@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Oct 2007 13:56:18 -0700
X-Trace: forums-1-dub 1193345778 10.22.241.41 (25 Oct 2007 13:56:18 -0700)
X-Original-Trace: 25 Oct 2007 13:56:18 -0700, 10.22.241.41
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6489
Article PK: 4781

I have your book, just didn't have it with me. I'll have to
re-read it when I get home, but got it to work. Thanks.

> Short answer: buy my book :)
>
> Long answer: you may be happier with a row-level trigger
> since then you can treat REFERENCING NEW AS name as a
> single "record" with dot notation.
>
> Caveat Emptor: I have not tested the following syntax:
>
> ALTER TRIGGER "AddJCVendorId" BEFORE INSERT
> ORDER 1 ON "DBA"."Subcontractors"
> REFERENCING NEW AS newsub
> FOR EACH ROW
> BEGIN
> update jobcost set jobcost.vendorid =
> newsub.vendorid,jobcost.contract_amount =
> newsub.sc,jobcost.contract_no =
> newsub.contract_no
> where jobcost.job = newsub.job and jobcost.subjob
> = newsub.subjob and jobcost.costcode =
> newsub.costcode and jobcost.costtype =
> newsub.costtype;
> END;
>
> Breck
>
> On 25 Oct 2007 09:15:32 -0700, Geoffrey Chambers wrote:
>
> >I'm new to the idea of triggers, but would like to know
> if >I'm headed in the right direction. I have a 2 tables,
> when a >new row is added to subcontractors, I would like
> to update >fields in the jobcost table based on the row
> added in the >first table. I tried to create a trigger,
> but I'm a little >confused on how I can restrict the
> update on the second >table. I created the following
> trigger but really don't see >how it would know the values
> of job,subjob,costcode,costtype >of the newly created row
> for subcontractors that was just >added.
> >
> >ALTER TRIGGER "AddJCVendorId" AFTER INSERT
> >ORDER 1 ON "DBA"."Subcontractors"
> >/* REFERENCING NEW AS new_name */
> >FOR EACH STATEMENT /* WHEN( search_condition ) */
> >BEGIN
> > update jobcost set jobcost.vendorid =
> >subcontractors.vendorid,jobcost.contract_amount =
> >subcontractors.sc,jobcost.contract_no =
> >subcontractors.contract_no
> > where jobcost.job = subcontractors.job and
> jobcost.subjob >= subcontractors.subjob and
> jobcost.costcode = >subcontractors.costcode and
> jobcost.costtype = >subcontractors.costtype
> >END
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
>
http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com