Results 1 to 10 of 10

Thread: trigger problem

  1. #1
    Join Date
    Aug 2010
    Posts
    5

    Red face Unanswered: trigger problem

    am using ERwin data modeler to design the er diagram of my database
    and by using forward engineering feature i generated the database but some triggers are not running ... please help me out

    the triggers are

    CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0)
    AND
    ((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0))


    CREATE TRIGGER tU_cust_info NO CASCADE BEFORE UPDATE ON cust_info
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM cust_info WHERE cust_info.cust_id <> :new.cust_id) > 0) AND
    ((SELECT count(*) FROM taxi_booking_details WHERE taxi_booking_details.cust_id = old.cust_id) > 0))


    CREATE TRIGGER tU_taxi_booking_de NO CASCADE BEFORE UPDATE ON taxi_booking_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN ((SELECT count(*) FROM taxi_booking_details WHERE :new.tx_bukng_id <> :old.tx_bukng_id) > 0)
    UPDATE taxi_booked
    SET
    taxi_booked.tx_bukng_id = new.tx_bukng_id
    WHERE
    taxi_booked.tx_bukng_id = old.tx_bukng_id


    CREATE TRIGGER tU_taxi_details NO CASCADE BEFORE UPDATE ON taxi_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id <> :new.city_id
    OR
    taxi_details.tx_id <> :new.tx_id) > 0)
    AND
    ((SELECT count(*) FROM taxi_booked WHERE taxi_booked.city_id = old.city_id
    AND
    taxi_booked.tx_id = old.tx_id) > 0))
    Last edited by anuj_khanna; 08-31-10 at 06:18.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the DDLs of tables(city_details, taxi_details, cust_info, taxi_booking_details, taxi_booked) and indexes.

    ... but some triggers are not running ...
    How did you know some are not running?
    If you got some error/warning message(s), please show error codes and full message texts.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the point of all the triggers, except the 3rd? They just have a WHEN clause but nothing in the trigger body. So there is no triggered action at all.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2010
    Posts
    5
    @TONKUMA

    the error message of first trigger is as follows>>>

    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0) AND
    ((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0));
    ------------------------------------------------------------------------------
    CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0) AND
    ((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0))
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token ":new" was found following "y_details.city_id
    <>". Expected tokens may include: "<name>". LINE NUMBER=3. SQLSTATE=42601

    SQL0104N An unexpected token ":new" was found following "y_details.city_id <>". Expected tokens may include: "<name> ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be processed.

    User response:

    Examine and correct the statement in the area of the specified token.

    sqlcode: -104

    sqlstate: 42601

  5. #5
    Join Date
    Aug 2010
    Posts
    5
    @stolze

    thank you sir,

    then what's the problem in the third one?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    anuj_khanna

    Error message gave you enough information.
    SQL0104N An unexpected token ":new" was found following "y_details.city_id <>". Expected tokens may include: "<name> ".
    ":new" is invalid. Remove colon(":").

    You should study basic of SQL on DB2 by using some entry books.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by anuj_khanna View Post
    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> :new.city_id) > 0) AND
    ((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0));
    ------------------------------------------------------------------------------
    What is the purpose of an empty trigger? It does not seem to do anything, and I doubt it will compile.

  8. #8
    Join Date
    Aug 2010
    Posts
    5
    @tonkuma

    sir/madam

    i have already tried that. but there was an error and that was>>>

    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> new.city_id) > 0) AND
    ((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0));
    ------------------------------------------------------------------------------
    CREATE TRIGGER tU_city_details NO CASCADE BEFORE UPDATE ON city_details
    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL
    WHEN (((SELECT count(*) FROM city_details WHERE city_details.city_id <> new.city_id) > 0) AND
    ((SELECT count(*) FROM taxi_details WHERE taxi_details.city_id = old.city_id) > 0))
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "=
    old.city_id) > 0))". Expected tokens may include: "<combined_trigger_body>".
    LINE NUMBER=4. SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "= old.city_id) > 0))". Expected tokens may include: "<combined_trigger_body> ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be processed.

    User response:

    Examine and correct the statement in the area of the specified token.

    sqlcode: -104

    sqlstate: 42601

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    anuj_khanna,

    PLEASE read replies of stolze and n_i.

  10. #10
    Join Date
    Aug 2010
    Posts
    5
    to all

    sorry for lesser information.... actually am novice in databases and started with DB2 ... i posted my question here cause one one can easily got solution of its problems by posting on such forums.

    i got solution of three of my problems.

    but what is the problem with 3rd one trigger?

Posting Permissions

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