Results 1 to 8 of 8

Thread: Trigger Help !

  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: Trigger Help !

    Hey everyone , i am relatively new in triigers and i was hoping for some help on this one.

    I have succefully created a triger to check for the email correctness.

    And i want one more to check that the same product is not entered twice on a day.

    I have 3 tables. the products, the catalog and the entry.

    So i want to add a produnt into a given catalog but i do not want a product to be entered twich on the same date

    How can i do that with triggers ?
    the date is on the catalog table and whenever i enter an entry ill have to check that the day is not the same.

    Some help would be greatly appreciated !

  2. #2
    Join Date
    Dec 2007
    Posts
    3
    CREATE TRIGGER check_date
    BEFORE INSERT ON entry
    REFERENCING NEW AS n
    FOR EACH ROW MODE
    BEGIN ATOMIC
    FROM product s, catalog x, entry e
    WHERE s.sno = e.esno and
    e.encode = x.excode and
    n.x.date = x.date
    SIGNAL SQLSTATE '75001'
    (' There cannot be two entries on the same day');
    END


    This is something that i am trying to do, but it is wrong, i didnt run it, cause i now it is wrong , so some help

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why don't you just create a unique index on the columns?

    Andy

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
     FROM product s, catalog x, entry e
    WHERE s.sno = e.esno and
    e.encode = x.excode and
    n.x.date = x.date
    SIGNAL SQLSTATE '75001'
    (' There cannot be two entries on the same day');
    does not look like a complete SQL statement.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2007
    Posts
    3
    well a unique is one way to solve that one, as also doing that programmatically ( java )

    but i am looking for a trigger solution.


    So what else should i include in order to make it a complete sql ?

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    CREATE TRIGGER NyTrig NO CASCADE BEFORE INSERT ON MyTable
    REFERENCING NEW as newdata FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    DECLARE num_rows int;

    SET num_rows = (select count(*) from MyTable as mt where (mt.col1 = newdata.col1 and mt.col2 = newdata.col2) );

    if (num_rows > 0)
    THEN SIGNAL SQLSTATE '75001' set message_text = ' There cannot be two entries on the same day';
    END IF;

    END


    Although, in may opinion, a unique constraint / index is the better way to go.

    Andy

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A trigger is pretty much the worst choice. A unique constraint is supported by an index. With the index, a check for duplicates is trivial and done with a few page access only. With a trigger, you potentially have to read all data pages.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As the others have sad, index is the best choice for performance reasons.

    In case you have to go the trigger route, a variation of the trigger stmt: ( sorry, didn't test it)

    Code:
    CREATE TRIGGER NyTrig NO CASCADE BEFORE INSERT ON MyTable
    REFERENCING NEW as newdata  FOR EACH ROW  MODE DB2SQL
    WHEN EXISTS (select 1 from MyTable as mt where (mt.col1 = newdata.col1 and mt.col2 = newdata.col2) )
     
         SIGNAL  SQLSTATE '75001' set message_text = ' There cannot be two entries on the same day'
    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
  •