Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Unanswered: LOAD-Replace and DELETE-Trigger

    Hi

    Is it possible to activate a DELETE-Trigger when loading data with the replace-option ? In the manual it says about the replace-option: ... deletes all existing data from the table, ...

    So, I did the following:

    create trigger ADU.BESTANDDATUM after delete on ADU.UTADU_ADRESSE
    for each statement mode db2sql
    begin atomic
    update ADU.UTADU_DYN_ATTRIBUT set (WERT_INT) = month(current timestamp) where (NAME, ART) = ('ADU_BESTAND_DATUM_MONAT', 11) ;
    end

    db2 LOAD FROM UTADU_ADRESSE.IXF OF IXF REPLACE INTO ADU.UTADU_ADRESSE STATISTICS YES AND INDEXES ALL NONRECOVERABLE

    -> The trigger wasn't activated, so I think it must be a SQL-DELETE to work. Or is it possible to activate a trigger on a LOAD ???

    (UDB 7.1, Unix/Windows)

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The load with replace option does not acutally issue SQL deletes to get rid of the existing data in the table. That is why the trigger is not executed.

  3. #3
    Join Date
    Jul 2003
    Posts
    50
    So, there is no posibility to activate a trigger by a LOAD ?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    According to the manual:

    "The import utility adds rows to the target table using the SQL INSERT
    statement. The utility issues one INSERT statement for each row of data in the input file."

    But you would have to try it to see if it works the way you want.

  5. #5
    Join Date
    Dec 2002
    Posts
    134
    Originally posted by c149187
    So, there is no posibility to activate a trigger by a LOAD ?
    Triggers are not supported in LOAD(it's understandble as load does not go through regular route for inserting data). You have to use IMPORT, on order to fire up triggers.

    http://www-3.ibm.com/cgi-bin/db2www/...n=r0004639.htm

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    But with an IMPORT REPLACE it "may" not execute any triggers. Here is an excerpt from the manual:

    "REPLACE
    Deletes all existing data from the table by truncating the data object,
    and inserts the imported data."

  7. #7
    Join Date
    Dec 2002
    Posts
    134
    Originally posted by Marcus_A
    But with an IMPORT REPLACE it "may" not execute any triggers. Here is an excerpt from the manual:

    "REPLACE
    Deletes all existing data from the table by truncating the data object,
    and inserts the imported data."
    I did small test and you are correct, no triggers for import with replace.

    I did not pay attention to the recommedations to do import with empty file to delete all rows (I was always using load).

    -dmitri

Posting Permissions

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