Hi guys!

Could any one help me to create a trigger that has a cursor on it to identify if new data i will insert but if a data has been insert already i will just update?

Thanks,
Life is short.

In DB2 9, just use "MERGE" for this.
If you are using an older version of DB2, you could try to create a "before" trigger to catch the insert, but I don't see how you could make it do the update instead, since the before trigger will have to induce a negative SQLCODE hence no changes are performed; and otherwise the INSERT fails and again no changes are performed.
The only solution I see is creating a stored procedure which contains the following sequential logic:
- test whether the PK is already present, e.g. by doing the insert and checking the SQLCODE.
- do the insert when the PK was not yet present
- do any sort of UPDATE instead when the PK is present.

consider using an update-statement and an "AFTER UPDATE"-trigger which inserts the data if the primary key is not found.
But - as Peter.Vanroose already mentioned - a stored procedure might be the better alternative.

Hi,
"merge" is also available in v8. This was one of new functionalities of v8.
Hope this helps,
Grofaty

Only in v8 for LUW -- v8 for z/OS does not have MERGE, but version 9 has.
(It was not clear from the original posting which platform this was referring to.)

CREATE TRIGGER ADMINISTRATOR.WAFERINSERT NO CASCADE BEFORE INSERT ON ADMINISTRATOR.SLDR_RAW1 REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
WHEN(EXISTS
(SELECT lot_no,waferrow,class,jobnum,op,txnts,userid,tool, product
FROM ADMINISTRATOR.SLDR_RAW1
WHERE lot_no = NEW.lot_no
AND waferrow = NEW.waferrow
AND class = NEW.class
AND op = NEW.op
AND tool = NEW.tool
AND product = NEW.product))
BEGIN ATOMIC
SET txnts = NEW.txnts;
SET good = NEW.good;
SET product = NEW.product;
SET tool = NEW.tool;
SET userid = NEW.userid;
END

This is what i've made but i didn't work at all.
I use db2 import to send csv file
mainly i will do this regular insertion about 300,000 rows a day.
but if i will use a procedure i think it would be slow

Can you give me suggestion?

Thanks,
mabeljovan

If you are on v8 for Linux/Unix/Windows, I would suggest you to use MERGE.

Thanks,

But can I use merge for a single table only?

Regards,
mabeljovan

Could i use it on a trigger/procedure/function or a script?

Regards,
mabeljovan