Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Tricky Check Constraint

    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.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Tricky Check Constraint

    A trigger may help

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Tricky Check Constraint

    Originally posted by ansonee
    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" ;


    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.
    That would be

    CREATE TABLE CHANNELSESSION (
    ...
    ACTIVESESSION VARCHAR (1) CONSTRAINT CK_SESS CHECK (NOT (ACTIVESESSION = 'N'))
    ...


    By the way, VARCHAR(1) does not make any sense...

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Tricky Check Constraint

    Originally posted by n_i
    That would be

    CREATE TABLE CHANNELSESSION (
    ...
    ACTIVESESSION VARCHAR (1) CONSTRAINT CK_SESS CHECK (NOT (ACTIVESESSION = 'N'))
    ...


    By the way, VARCHAR(1) does not make any sense...
    Oops, it seems I've misunderstood the question....

    The point about VARCHAR(1) still stands though

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Re: Tricky Check Constraint

    Originally posted by sathyaram_s
    A trigger may help

    Cheers
    Sathyaram
    are you talking about something like a before update or before insert trigger?
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Tricky Check Constraint

    Yes .. A before trigger

    Sathyaram

    Originally posted by ansonee
    are you talking about something like a before update or before insert trigger?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    I've been looking for examples but haven't been able to find anything that does what I'm looking for. Coiuld you provide some insight as to how to go about implementing this pseudo check constraint?

    Again, there's a stored procedure that is going to insert a row, but if a certain condition exists, that row should not be inserted.

    Thanks for any help you can provide.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  8. #8
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  9. #9
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    I have also tried donig the checking from within the stored procedure:

    CREATE PROCEDURE AMROBI2.CreateAIMConnection (IN decCHANNELSESSIONID DECIMAL(12,0),
    IN strBUSINESSLINE VARCHAR(24),
    IN strBRANCHID VARCHAR(24),
    OUT decAIMCONNECTIONID DECIMAL(12,0))
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    IF (SELECT AMROBI2.CHANNELSESSION.ACTIVESESSION FROM AMROBI2.CHANNELSESSION WHERE CHANNELSESSION.CHANNELSESSIONID = decCHANNELSESSIONID) = 'Y'
    THEN
    INSERT INTO AMROBI2.AIMCONNECTION (ChannelSessionID, BusinessLine, BranchID, StartDateTime)
    VALUES
    (decCHANNELSESSIONID, strBUSINESSLINE, strBRANCHID, CURRENT TIMESTAMP);
    SET decAIMCONNECTIONID = IDENTITY_VAL_LOCAL();
    ELSE SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS');
    END IF;
    END P1

    It looks like DB2 doesn't like the SIGNAL SQLSTATE within a stored procedure....

    Thanks in advance for any help you guys may render...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Did you run your SQLs the same way as shown here ?


    I assume it will run if you create a file , say trigger.sql (note the statement deliniters):

    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 @

    and execute this using

    db2 -td@ -f trigger.sql
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The same problem with your SP

    ie -t tells db2 that the statement delimiter is the default(
    -td@ tells that the delimiter is @

    The only statement delimiteres within triggers and procedures is ; , so in out command line execution , we'll have to change the delimiters

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    It will run, it's the CHANNELSESSION.CHANNELSESSIONID not being valid in the context that is giving the problems. The error states:

    For a CREATE TRIGGER statement:

    - A reference is made to a column of the subject table without
    using an OLD or NEW correlation name.

    - The left hand side of an assignment in the SET
    transition-variable statement in the triggered action
    specifies an old transition variable where only a new
    transition variable is supported.

    This is what causes me to think there isn't a way to accomplish what I want via a trigger, if you need to always reference OLD and NEW in the trigger.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    See if this trigger does your job ...

    (My apologies, if this doesn't)

    CREATE TRIGGER AIMP.VALIDATE2 NO CASCADE
    BEFORE INSERT ON AIMD.AIMCONNECTION
    REFERENCING NEW AS NEWROWS
    FOR EACH ROW
    MODE DB2SQL
    WHEN (EXISTS(SELECT * FROM aimd.CHANNELSESSION WHERE CHANNELSESSIONID=NEWROWS.CHANNELSESSIONID and ACTIVESESSION='N'))
    BEGIN ATOMIC

    SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS');

    END @
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can even omit the BEGIN ATOMIC and END and change select * to select 1 :

    CREATE TRIGGER AIMP.VALIDATE2 NO CASCADE
    BEFORE INSERT ON AIMD.AIMCONNECTION
    REFERENCING NEW AS NEWROWS
    FOR EACH ROW
    MODE DB2SQL
    WHEN (EXISTS(SELECT 1 FROM aimd.CHANNELSESSION WHERE CHANNELSESSIONID=NEWROWS.CHANNELSESSIONID and ACTIVESESSION='N'))
    SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS')
    @

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  15. #15
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I think even an after trigger will not harm or may even perform better

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •