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 for static view after join...

3 posts in Trigger Last posting was on 2007-03-15 20:33:15.0Z
Charlie Stoll Posted on 2007-02-08 23:03:09.0Z
Reply-To: "Charlie Stoll" <charliestoll@comcast.net>
From: "Charlie Stoll" <charliestoll@comcast.net>
Newsgroups: advantage.trigger
Subject: Trigger for static view after join...
Date: Thu, 8 Feb 2007 18:03:09 -0500
Lines: 33
Organization: Astec, Inc.
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 198.60.252.16
Message-ID: <45cbab22@solutions.advantagedatabase.com>
X-Trace: 8 Feb 2007 15:58:42 -0700, 198.60.252.16
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.60.252.16
Xref: solutions.advantagedatabase.com Advantage.Trigger:307
Article PK: 1136373

Hi,

I was wondering if someone could give me an example of how to set up the
appropriate triggers in a static view. I'm JOINing multiple tables for
viewing/editing by the user. here's a sample of my query code:

SELECT
--choose fields from mixform table
MF.MIX, MF.CHANNEL_NAME, MF.MATERIAL, MF.TARGET_PERCENT, MF.START_TIME,
MF.STOP_TIME,
--choose fields from tcmixform (created by CS to hold TCII-specific data)
table
TCMF.ENABLED,
--choose fields from material table
MAT.DENSITY, MAT.MOISTURE
FROM MIXFORM MF LEFT OUTER JOIN TCMIXFORM TCMF
--link mixform and tcmixform by mix id and channel number
ON (MF.MIX = TCMF.mix)
AND (MF.CHANNEL_NUMBER = TCMF.CHANNEL_NUMBER)
LEFT OUTER JOIN MATERIAL MAT
--link mixform table and material by material name
ON (MF.MATERIAL = MAT.MATERIAL)
--select records that match parameter value for mix id
WHERE MF.MIX = :MixID;

Any help would be greatly appreciated.

Thanks,

Charlie


Jeremy Mullin Posted on 2007-02-20 15:35:12.0Z
Date: Tue, 20 Feb 2007 15:35:12 +0000 (UTC)
Message-ID: <886edc15244f18c922e12c51fa72@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Trigger for static view after join...
Newsgroups: Advantage.Trigger
References: <45cbab22@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 928.2
NNTP-Posting-Host: 10.24.38.124
X-Trace: 20 Feb 2007 08:30:47 -0700, 10.24.38.124
Lines: 71
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.124
Xref: solutions.advantagedatabase.com Advantage.Trigger:321
Article PK: 1136387

Hi Charlie, I'm not testing this, just writing it directly in this post,
so there may be some issues, but it should give you the general idea. I'm
just providing an update trigger, you will get the idea and can then write
the delete and insert triggers. You may still need to address things like
key generation for the insert statements (or you may want to handle inserts
separately from updates and deletes, they often require more information
than you have in the view fields).

INSTEADOF UPDATE
_________________
declare new cursor as select * from __new;
open new;

try
fetch new;

-- update mixform
update mixform set channel_name = new.channel_name,
material = new.material,
target_percent = new.target_percent,
start_time = new.start_time,
stop_time = new.stop_time where mix = new.mix;

-- update tcmf
-- exercise left for the reader :)

-- update materials
-- exercise left for the reader :)
finally
close new;
end try;



J.D. Mullin
Advantage R&D

> Hi,
>
> I was wondering if someone could give me an example of how to set up
> the appropriate triggers in a static view. I'm JOINing multiple tables
> for viewing/editing by the user. here's a sample of my query code:
>
> SELECT
> --choose fields from mixform table
> MF.MIX, MF.CHANNEL_NAME, MF.MATERIAL, MF.TARGET_PERCENT,
> MF.START_TIME,
> MF.STOP_TIME,
> --choose fields from tcmixform (created by CS to hold TCII-specific
> data)
> table
> TCMF.ENABLED,
> --choose fields from material table
> MAT.DENSITY, MAT.MOISTURE
> FROM MIXFORM MF LEFT OUTER JOIN TCMIXFORM TCMF
> --link mixform and tcmixform by mix id and channel number
> ON (MF.MIX = TCMF.mix)
> AND (MF.CHANNEL_NUMBER = TCMF.CHANNEL_NUMBER)
> LEFT OUTER JOIN MATERIAL MAT
> --link mixform table and material by material name
> ON (MF.MATERIAL = MAT.MATERIAL)
> --select records that match parameter value for mix id
> WHERE MF.MIX = :MixID;
> Any help would be greatly appreciated.
>
> Thanks,
>
> Charlie
>


Charlie Stoll Posted on 2007-03-15 20:33:15.0Z
From: "Charlie Stoll" <cstoll@astecinc.com>
Newsgroups: Advantage.Trigger
References: <45cbab22@solutions.advantagedatabase.com> <886edc15244f18c922e12c51fa72@devzone.advantagedatabase.com>
Subject: Re: Trigger for static view after join...
Date: Thu, 15 Mar 2007 16:33:15 -0400
Lines: 80
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 12.177.82.200
Message-ID: <45f9ac7c@solutions.advantagedatabase.com>
X-Trace: 15 Mar 2007 14:28:44 -0700, 12.177.82.200
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!12.177.82.200
Xref: solutions.advantagedatabase.com Advantage.Trigger:325
Article PK: 1136392

That looks great.

I'll give it a go shortly. Thanks for the direction!

Charlie

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc15244f18c922e12c51fa72@devzone.advantagedatabase.com...
> Hi Charlie, I'm not testing this, just writing it directly in this post,
> so there may be some issues, but it should give you the general idea. I'm
> just providing an update trigger, you will get the idea and can then write
> the delete and insert triggers. You may still need to address things like
> key generation for the insert statements (or you may want to handle
> inserts separately from updates and deletes, they often require more
> information than you have in the view fields).
>
> INSTEADOF UPDATE
> _________________
> declare new cursor as select * from __new;
> open new;
>
> try
> fetch new;
>
> -- update mixform
> update mixform set channel_name = new.channel_name, material =
> new.material, target_percent = new.target_percent,
> start_time = new.start_time,
> stop_time = new.stop_time where mix = new.mix;
>
> -- update tcmf
> -- exercise left for the reader :)
>
> -- update materials
> -- exercise left for the reader :)
> finally
> close new;
> end try;
>
>
>
> J.D. Mullin
> Advantage R&D
>
>> Hi,
>>
>> I was wondering if someone could give me an example of how to set up
>> the appropriate triggers in a static view. I'm JOINing multiple tables
>> for viewing/editing by the user. here's a sample of my query code:
>>
>> SELECT
>> --choose fields from mixform table
>> MF.MIX, MF.CHANNEL_NAME, MF.MATERIAL, MF.TARGET_PERCENT,
>> MF.START_TIME,
>> MF.STOP_TIME,
>> --choose fields from tcmixform (created by CS to hold TCII-specific
>> data)
>> table
>> TCMF.ENABLED,
>> --choose fields from material table
>> MAT.DENSITY, MAT.MOISTURE
>> FROM MIXFORM MF LEFT OUTER JOIN TCMIXFORM TCMF
>> --link mixform and tcmixform by mix id and channel number
>> ON (MF.MIX = TCMF.mix)
>> AND (MF.CHANNEL_NUMBER = TCMF.CHANNEL_NUMBER)
>> LEFT OUTER JOIN MATERIAL MAT
>> --link mixform table and material by material name
>> ON (MF.MATERIAL = MAT.MATERIAL)
>> --select records that match parameter value for mix id
>> WHERE MF.MIX = :MixID;
>> Any help would be greatly appreciated.
>>
>> Thanks,
>>
>> Charlie
>>
>
>