Hi there,
I am new to triggers and SP(never written an SP) but the 1st trigger on insert worked however the following does not work.

CREATE TRIGGER VNAUSER.DEL_LOCATION
AFTER INSERT OF LOCATION ON VNAUSER.PSHR1TEMP
REFERENCING OLD AS OLD_TAB
NEW AS NEW_TAB
FOR EACH ROW MODE DB2SQL
DELETE FROM VNAUSER.TBLLOCATION;
INSERT INTO VNAUSER.TBLLOCATION(Location) select distinct location from VNAUSER.PSHR1TEMP;

What I am trying to do with above Trigger is as soon as there in an insert in TBLPSHR1TEMP, the existing TBLLOCATION date should be deleted and a fresh data on Location should be added..However the risk that I realized is, if someone is accessing the TBLLocation.
However there is solution which I am finding it difficult to incorporate.

All I need to check is if there is a location in TBLPSHR1TEMP which is not there in TBLLOCATION and then its only an INSERT and NOT A DELETE of TBLLOCATION DATA

kindly provide guidance as to this
regards
aba

Try the MERGE statement instead.

Andy

The MERGE seems to be a good option