Results 1 to 7 of 7

Thread: DB2 Trigger

  1. #1
    Join Date
    Aug 2003
    Location
    France
    Posts
    7

    Unanswered: DB2 Trigger

    Hi,

    I want to create a trigger that check after insert in hotel table if id_hotel exist in table hotelroomtype when not then insert it like that:

    CONNECT TO SHOWCASE user db2inst1 using db2inst1-4tco;

    DROP TRIGGER HOTEL_INSERT;

    CREATE TRIGGER HOTEL_INSERT
    AFTER INSERT ON HOTEL
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    IF (SELECT count(*) from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) > 0 THEN (INSERT INTO DB2INST1.HOTELROOMTYPE (id_hotel,id_roomtype,priceprenight) VALUES (n.ID,1,150));
    END IF;

    when I try to create that trigger I have follow errorsB21034E 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 "INSERT INTO" was found following "tel=N.ID) > 0
    THEN (". Expected tokens may include: "<values>". LINE NUMBER=5.
    SQLSTATE=42601

    END IF
    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 "END IF".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

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

    Re: DB2 Trigger

    Try this:


    IF EXISTS (SELECT ID_HOTEL from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID)
    THEN
    INSERT INTO DB2INST1.HOTELROOMTYPE (id_hotel,id_roomtype,priceprenight) VALUES (n.ID,1,150));
    END IF;

  3. #3
    Join Date
    Aug 2003
    Location
    France
    Posts
    7

    Re: DB2 Trigger

    Thanks,

    it not the solution I think the errors come from a syntax error when I start my script on the dos promt with : db2 -tvf scriptname.sql

    PSN



    Originally posted by n_i
    Try this:


    IF EXISTS (SELECT ID_HOTEL from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID)
    THEN
    INSERT INTO DB2INST1.HOTELROOMTYPE (id_hotel,id_roomtype,priceprenight) VALUES (n.ID,1,150));
    END IF;

  4. #4
    Join Date
    Mar 2004
    Location
    Belarus
    Posts
    5
    Hi!

    Since you do not use a single SQL statement you have to write

    CREATE TRIGGER HOTEL_INSERT
    AFTER INSERT ON HOTEL
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL

    BEGIN ATOMIC

    IF (SELECT count(*) from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) > 0 THEN (INSERT INTO DB2INST1.HOTELROOMTYPE (id_hotel,id_roomtype,priceprenight) VALUES (n.ID,1,150));
    END IF;

    END
    @ -- here is command separator (not the semicolon)


    Regards,
    Andrew

  5. #5
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: DB2 Trigger

    FYI, another way to write this trigger:
    CREATE TRIGGER HOTEL_INSERT
    AFTER INSERT ON HOTEL
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    WHEN ( (SELECT 1 FROM DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) = 1 )
    INSERT ......
    END
    @

    Originally posted by psn
    Hi,

    I want to create a trigger that check after insert in hotel table if id_hotel exist in table hotelroomtype when not then insert it like that:

    CONNECT TO SHOWCASE user db2inst1 using db2inst1-4tco;

    DROP TRIGGER HOTEL_INSERT;

    CREATE TRIGGER HOTEL_INSERT
    AFTER INSERT ON HOTEL
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    IF (SELECT count(*) from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) > 0 THEN (INSERT INTO DB2INST1.HOTELROOMTYPE (id_hotel,id_roomtype,priceprenight) VALUES (n.ID,1,150));
    END IF;

    when I try to create that trigger I have follow errorsB21034E 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 "INSERT INTO" was found following "tel=N.ID) > 0
    THEN (". Expected tokens may include: "<values>". LINE NUMBER=5.
    SQLSTATE=42601

    END IF
    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 "END IF".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

  6. #6
    Join Date
    Mar 2004
    Location
    Athens, Hellas
    Posts
    3
    Hi,
    I'm writting a similar trigger and I wanted to ask you what is the difference (if there is one) in terms of performance, between the three approaches:

    1. IF EXISTS (SELECT ID_HOTEL from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID)
    THEN

    2. WHEN ( (SELECT 1 FROM DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) = 1 )

    3. IF (SELECT count(*) from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) > 0 THEN (INSERT INTO DB2INST1.HOTELROOMTYPE (id_hotel,id_roomtype,priceprenight) VALUES (n.ID,1,150));

    I guess 3 is the slowest and since in my trigger I only want to check the existence of 1 row to use it as a filter (and not actually select it) I should use one of the other two solutions. Do I gain anything if I combine 1 and 2 like this:

    IF EXISTS( SELECT 1 FROM DB2INST1.HOTELROOMTYPE where id_hotel=N.ID)


    Thanks
    Kostas

  7. #7
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Using WHEN clause makes the evaluation occur immediately and the determined outcome happens before entering into the trigger code. So WHEN is false, DB2 leaves the trigger. It can be taken as a style technique, but when you have multiple triggers on a table, it definitely helps in performance.


    Originally posted by kcb
    Hi,
    I'm writting a similar trigger and I wanted to ask you what is the difference (if there is one) in terms of performance, between the three approaches:

    1. IF EXISTS (SELECT ID_HOTEL from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID)
    THEN

    2. WHEN ( (SELECT 1 FROM DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) = 1 )

    3. IF (SELECT count(*) from DB2INST1.HOTELROOMTYPE where id_hotel=N.ID) > 0 THEN (INSERT INTO DB2INST1.HOTELROOMTYPE (id_hotel,id_roomtype,priceprenight) VALUES (n.ID,1,150));

    I guess 3 is the slowest and since in my trigger I only want to check the existence of 1 row to use it as a filter (and not actually select it) I should use one of the other two solutions. Do I gain anything if I combine 1 and 2 like this:

    IF EXISTS( SELECT 1 FROM DB2INST1.HOTELROOMTYPE where id_hotel=N.ID)


    Thanks
    Kostas

Posting Permissions

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