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)

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.

So, there is no posibility to activate a trigger by a LOAD ?

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.

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

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