Anthony Robinson wrote:
[color=blue]
> Blair Adamache <badamache@2muchspam.yahoo.com> wrote in message news:<bvub9g$h8m$1@hanover.torolab.ibm.com>...
>[color=green]
>>There's one BEFORE example under CREATE TRIGGER in the SQL Reference
>>(you can search the docs on STOCK_STATUS). There are trigger articles
>>here as well:
>>
>>
http://www-136.ibm.com/developerworks/db2/
>>
>>But it might take some hunting to find lots of BEFORE examples.
>>
>>Anthony Robinson wrote:
>>
>>[color=darkred]
>>>Blair Adamache <badamache@2muchspam.yahoo.com> wrote in message news:<bvre7h$scm$1@hanover.torolab.ibm.com>...
>>>
>>>
>>>>The easiest way to implement RI-type things that aren't quite
>>>>referential integrity is usually with a trigger.
>>>>
>>>>Anthony Robinson wrote:
>>>>
>>>>
>>>>
>>>>>Consider the following tables:
>>>>>
>>>>>CREATE TABLE "AIMD "."CHANNELSESSION" (
>>>>>"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
>>>>>( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
>>>>>"CONNECTSTARTTIME" TIMESTAMP NOT NULL ,
>>>>>"CONNECTENDTIME" TIMESTAMP ,
>>>>>"ACTIVESESSION" VARCHAR(1),
>>>>>"CHANNELID" DECIMAL(12,0) NOT NULL )
>>>>>IN "USERSPACE1" ;
>>>>>
>>>>>
>>>>>CREATE TABLE "AIMD "."AIMCONNECTION" (
>>>>>"AIMCONNECTIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
>>>>>( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
>>>>>"BRANCHID" VARCHAR(24) ,
>>>>>"USERID" VARCHAR(24) ,
>>>>>"STARTDATETIME" TIMESTAMP NOT NULL ,
>>>>>"ENDDATETIME" TIMESTAMP ,
>>>>>"TERMINATIONREASON" VARCHAR(24) ,
>>>>>"BUSINESSLINE" VARCHAR(24) ,
>>>>>"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL )
>>>>>IN "USERSPACE1" ;
>>>>>
>>>>>There is a froeiegn key relationship between these two tables:
>>>>>CHANNELSESSION.CHANNELSESSIONID parent key and froeign key in
>>>>>AIMCONNECTION.
>>>>>
>>>>>My question is this: is it possible to create a check constraint that
>>>>>double checks the value of an attribute that is not a part of the
>>>>>foreing key. For instance, there may be an existing parent key in
>>>>>CHANNELSESSION, but if the value of ACTIVESESSION in the corresponding
>>>>>row is "N", then the row should not be able to be inserted.
>>>>>
>>>>>Hope that made sense.
>>>
>>>
>>>I've been looknig for examples of a BEFORE INSERT trigger and can't
>>>seem to find any godd ones. Any ideas where to look?[/color][/color]
>
>
> Blair:
>
> I've been tinkering around with the whole triggerthing and cam up with
> this:
>
> CONNECT TO AIM2;
> CREATE TRIGGER AIMP.VALIDATE NO CASCADE BEFORE INSERT ON
> AIMD.AIMCONNECTION REFERENCING NEW AS NEWROWS FOR EACH ROW MODE
> DB2SQL WHEN (NEWROWS.CHANNELSESSIONID =
> CHANNELSESSION.CHANNELSESSIONID)
> BEGIN ATOMIC
> IF CHANNELSESSION.ACTIVESESSION = 'N' THEN
> SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS');
> END IF;
> END;
> CONNECT RESET;
>
> When I try to create it I get an error stating that
> CHANNELSESSION.CHANNELSESSIONID is not valid in the context being
> used.
>
> Hopefully you or someone can shed some light on how I can go about
> accomplishing this:
>
> Before a row is inserted into AIMCONNECTION, I want to check the
> parent row in the CHANNELSESSION table (foreign key is
> CHANNELSESSIONID). If the value of ACTIVESESSION is 'N', then raise an
> error and not insert the new row. If the value is 'Y', then go ahead
> and insert the row.
>
> Is my syntax totally off? Or do I need to use an INSTEAD OF trigger
> instead?
>
> Thanks again for any assistance.[/color]
CREATE TRIGGER AIMP.VALIDATE NO CASCADE BEFORE INSERT ON
AIMD.AIMCONNECTION REFERENCING NEW AS NEWROWS FOR EACH ROW MODE
DB2SQL WHEN ('N' = (SELECT S.ACTIVESESSION
FROM CHANNELSESSION AS S
WHERE NEWROWS.CHANNELSESSIONID
= S.CHANNELSESSIONID))
SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS');
Note that this trigger assumes that CHANNELSESSIONID exists in the first
place. You need a tad more tweaking to raise the signal if it doesn't
exist or is 'N'.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Pseudo Check Constraint 
