Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Insert Update Trigger Help

    Hello Experts

    I have to create a trigger for Keep Track of Inserts, Delete and Update of a table by recoding that change in another table

    Here is the scenario

    Refer attachments for Table Structure

    CASE 1
    I have 2 tables DTV_CHANNELS_TEST and DTV_CONTROL_TEST

    In DTV_CHANNELS_TEST when a user insert a record where ACTIVE_STATUS = 1
    I need to do the following action to DTV_CONTROL_TEST table using a trigger

    1. Count all the records where ACTIVE_STATUS =1 and insert to NO_OF_CHANNELS column
    2. Insert CURRENT TIMESTAMP to LAST_UPDATE_DATE column
    3. Insert CURRENT USER to USER_ENTERED column
    4. Insert CURRENT TIMESTAMP to DATE_ENTERED column

    This trigger should fire only if user insert a record with ACTIVE_STATUS = 1

    CASE 2

    When user update the records

    If user change the ACTIVE_STATUS
    Then
    NO_OF_CHANNELS should increment or decrement [When user change ACTIVE_STATUS = 1 or 0] + Insert CURRENT TIMESTAMP to LAST_UPDATE_DATE column

    If user change column except ACTIVE_STATUS
    Then
    Insert CURRENT TIMESTAMP to LAST_UPDATE_DATE column only

    SO i was stuck with the insert trigger...

    Plz note that DTV_CONTROL_TEST table maintain 1 column and it will update every time triggers fire

    Appreciate ur help
    Attached Thumbnails Attached Thumbnails 1.JPG   2.JPG  

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you tried creating any triggers for the above scenarios?

    If so, please post what you have tried. Makes it easier for the "Experts" to build on ;-)


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

  3. #3
    Join Date
    Sep 2011
    Posts
    85

    Question

    CREATE OR REPLACE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
    AFTER INSERT
    ON "DB2ADMIN"."DTV_CHANNELS_TEST"
    REFERENCING
    NEW AS "NEWROW"
    FOR EACH ROW
    INSERT INTO DTV_CONTROL_TEST
    (NO_OF_CHANNELS,LAST_UPDATE_DATE,USER_ENTERED,DATE _ENTERED)
    VALUES ((SELECT COUNT(*) FROM DTV_CHANNELS_TEST),CURRENT TIMESTAMP,CURRENT USER,CURRENT TIMESTAMP)
    WHERE NEWROW.ACTIVE_STATUS = 1;

    I have tried this way for the where statements in inserts

    This one created in the DB but when i try to insert
    ----
    com.ibm.db2.jcc.am.SqlException: An error occurred in a triggered SQL statement in trigger "DB2ADMIN.TRIG_DTV_CONTROL". Information returned for the error includes SQLCODE "-407", SQLSTATE "23502" and message tokens "TBSPACEID=2, TABLEID=272, COLNO=0".. SQLCODE=-723, SQLSTATE=09000, DRIVER=4.8.86
    Error saving data
    ----
    CREATE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
    AFTER INSERT
    ON "DB2ADMIN"."DTV_CHANNELS_TEST"
    REFERENCING
    NEW AS "NEWROW"
    FOR EACH ROW
    INSERT INTO DTV_CONTROL_TEST (NO_OF_CHANNELS,LAST_UPDATE_DATE,USER_ENTERED,DATE _ENTERED)
    SELECT (
    SELECT COUNT(*)
    FROM DTV_CHANNELS_TEST ),CURRENT TIMESTAMP,CURRENT USER,CURRENT TIMESTAMP
    FROM SYSIBM.SYSDUMMY1
    WHERE EXISTS (
    SELECT *
    FROM DTV_CHANNELS_TEST
    WHERE ACTIVE_STATUS = 1);

    both of them didn't work..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Use UPDATE statement instead of INSERT statement in the trigger.

  5. #5
    Join Date
    Sep 2011
    Posts
    85
    CREATE OR REPLACE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
    AFTER INSERT
    ON "DB2ADMIN"."DTV_CHANNELS_TEST"
    REFERENCING
    NEW AS "NEWROW"
    FOR EACH ROW
    UPDATE DTV_CONTROL_TEST
    SET NO_OF_CHANNELS = (SELECT COUNT(*) FROM DTV_CHANNELS_TEST WHERE ACTIVE_STATUS = 1),LAST_UPDATE_DATE = CURRENT TIMESTAMP , USER_ENTERED = CURRENT USER , DATE_ENTERED = CURRENT TIMESTAMP
    WHERE (SELECT ACTIVE_STATUS FROM DTV_CHANNELS WHERE ACTIVE_STATUS = 1) = 1;

    ERROR
    com.ibm.db2.jcc.am.SqlException: An error occurred in a triggered SQL statement in trigger "DB2ADMIN.TRIG_DTV_CONTROL_INSERT". Information returned for the error includes SQLCODE "-811", SQLSTATE "21000" and message tokens "".. SQLCODE=-723, SQLSTATE=09000, DRIVER=4.8.86
    Error saving data

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You get SQLCODE 407 because you have defined CTRL_ID as NOT NULL but not inserting anything into that field. Correct that one.

    a) One option - Instead of doing a count(*), why not add 1 to the NO_OF_CHANNELS when the active_status is 1 in the insert or update and subtract 1 when the active_status is 0.

    b) You must have a delete trigger also if you have to keep the count of active_status current.

    c) CREATE TRIGGER ...
    AFTER IJNSERT ..
    WHEN (NEWROW.ACTIVE_STATUS=1) THEN
    <your triggered action>

    This will avoid using the WHERE clause in the query.

    d) What is the purpose of the CONTROL table ? If it is to know the current status, then you have to use UPDATE. If it is for History then you can use INSERT.

    Hope this makes sense
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Your SQLCODE 811 is

    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
    VALUES INTO statement is more than one row.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Who's creating the table definitions and who creating these trigger specficiations ?


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

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQLCODE "-811" must be issued from this subselect.
    (SELECT ACTIVE_STATUS FROM DTV_CHANNELS WHERE ACTIVE_STATUS = 1)

    See sathyaram_s' post, especially...
    c) CREATE TRIGGER ...
    AFTER IJNSERT ..
    WHEN (NEWROW.ACTIVE_STATUS=1) THEN
    <your triggered action>

    This will avoid using the WHERE clause in the query.

  10. #10
    Join Date
    Sep 2011
    Posts
    85
    CREATE OR REPLACE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
    AFTER INSERT
    ON "DB2ADMIN"."DTV_CHANNELS_TEST"
    REFERENCING
    NEW AS "NEWROW"
    FOR EACH ROW
    WHEN (NEWROW.ACTIVE_STATUS=1) THEN
    INSERT INTO DTV_CONTROL_TEST (NO_OF_CHANNELS,LAST_UPDATE_DATE,USER_ENTERED,DATE _ENTERED)
    VALUES ((SELECT COUNT(*) FROM DTV_CHANNELS_TEST WHERE ACTIVE_STATUS = 1),CURRENT TIMESTAMP,CURRENT USER,CURRENT TIMESTAMP)
    ;

    ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "THEN INSERT" was found following "ROW.ACTIVE_STATUS=1)". Expected tokens may include: "<insert>". LINE NUMBER=1. SQLSTATE=42601

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    CREATE TRIGGER - IBM DB2 9.7 for Linux, UNIX, and Windows

    Have you seen this page?

    And you don't seem to have paid attention to SQLCODE -407
    You get SQLCODE 407 because you have defined CTRL_ID as NOT NULL but not inserting anything into that field. Correct that one.
    I'm afraid, you have to go back to the design/specification and understand what and why it has been done that way. There appear to be gaps, IMHO.
    Last edited by sathyaram_s; 09-13-11 at 07:00.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    WHEN (NEWROW.ACTIVE_STATUS=1) THEN
    INSERT INTO DTV_CONTROL_TEST ...
    See the syntax of triggered-action and Example 2: in CREATE TRIGGER statement
    CREATE TRIGGER - IBM DB2 9.7 for Linux, UNIX, and Windows

    Note: "BEGIN ATOMIC" and "END" are not neccesary for a triggered-action with one statement.

  13. #13
    Join Date
    Sep 2011
    Posts
    85
    Create or replace trigger "db2admin"."trig_dtv_control_insert"
    after insert
    on "db2admin"."dtv_channels_test"
    referencing
    new as "newrow"
    for each row
    when (newrow.active_status=1) then
    begin atomic
    insert into dtv_control_test (no_of_channels,last_update_date,user_entered,date _entered)
    values ((select count(*) from dtv_channels_test where active_status = 1),current timestamp,current user,current timestamp);
    end
    ;

    create trigger "db2admin"."trig_dtv_control_insert"
    after insert of channel_id, channel_logo, default_url, active_status, channel_type on "db2admin"."dtv_channels_test"
    referencing new as "newrow"
    for each row
    when (newrow.active_status=1)
    begin atomic
    insert into dtv_control_test (no_of_channels,last_update_date,user_entered,date _entered)
    values ((select count(*) from dtv_channels_test where active_status = 1),current timestamp,current user,current timestamp);
    end

  14. #14
    Join Date
    Sep 2011
    Posts
    85
    Im getting errors for above to methods

    DB2 Database Error: ERROR [428HV] [IBM][DB2/NT] SQL0969N There is no message text corresponding to SQL error "-20521" in the message file on this workstation. The error was returned from module "SQLNP02C" with original tokens "_entered 6". LINE NUMBER=1. SQLSTATE=428HV


    DB2 Database Error: ERROR [428HV] [IBM][DB2/NT] SQL0969N There is no message text corresponding to SQL error "-20521" in the message file on this workstation. The error was returned from module "SQLNP02C" with original tokens "_entered 6". LINE NUMBER=7. SQLSTATE=428HV

  15. #15
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is it possible that you have a space in
    ,date _entered)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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