AFTER INSERT ON TRIGGER

[db2admin]

Hello,

I lack knowledge about triggers.
I have created trigger on table A
Table A
-------------------------------------------------
ID INTEGER
CASEID INTEGER
CEID INTEGER
AS_CD CHAR(2)
FROM_DT DATE
THRU_DT DATE
UPDATE_TIME_STAMP TIMESTAMP
--------------------------------------------------------------
ID is primary key and caseid is foreign key. one of the THRU_DT is
NULL for each set of CASEID ( all rows with same CASEID). when a new
row is inserted into this table, FROM_DT of new row becomes THRU_DT of
last last row which has same CASEID. Application is supposed to take
care of inserting this value but sometime it does not do it. so we end
up with multiple rows having value NULL in THRU_DT column. I created
trigger which is as below
-------------------------------------------------------------------
CREATE TRIGGER abc
AFTER INSERT ON A A1
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

UPDATE A
SET A1.thru_dt = new.from_dt
WHERE A1.caseid = new.caseid
AND A1.as_cd = new.as_cd
AND A1.thru_dt IS NULL
AND A1.from_dt <= new.from_dt
AND A1.id =
(
SELECT MAX( A2.id )
FROM A A2
WHERE A2.caseid = new.caseid
AND A2.as_cd = new.as_cd
AND A2.id < new.id
);
END
-------------------------------------------------------
Our insert operations are very slow after we put this trigger on table
A.
Application try to update THRU_DT of last row before inserting new row
with same caseid. and when it inserts a row after updating, trigger
fires.
Does trigger get updated value of THRU_DT from last row with same
caseid and same as_cd or it gets old value because application ( java
code ) did not commit this whole transaction yet?
Regards

[Phil Sherman]

I'd setup a test case where you've inserted a new row and didn't update
the old THRU-DT. Run an explain on the update and see what the access
path is to do the work.

Without looking at statistics or information about the table size; I'd
guess that the update is using tablespace scan methodology to locate the
rows needed. Index use can be encouraged by the following:
1. Clustering index on CASEID.
2. Reorgs to keep the table clustered.
3. Appropriate management of freespace to maintain clustering between
reorgs.

Phil Sherman
db2admin wrote:

Hello,

I lack knowledge about triggers.
I have created trigger on table A
Table A
-------------------------------------------------
ID INTEGER
CASEID INTEGER
CEID INTEGER
AS_CD CHAR(2)
FROM_DT DATE
THRU_DT DATE
UPDATE_TIME_STAMP TIMESTAMP
--------------------------------------------------------------
ID is primary key and caseid is foreign key. one of the THRU_DT is
NULL for each set of CASEID ( all rows with same CASEID). when a new
row is inserted into this table, FROM_DT of new row becomes THRU_DT of
last last row which has same CASEID. Application is supposed to take
care of inserting this value but sometime it does not do it. so we end
up with multiple rows having value NULL in THRU_DT column. I created
trigger which is as below
-------------------------------------------------------------------
CREATE TRIGGER abc
AFTER INSERT ON A A1
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

UPDATE A
SET A1.thru_dt = new.from_dt
WHERE A1.caseid = new.caseid
AND A1.as_cd = new.as_cd
AND A1.thru_dt IS NULL
AND A1.from_dt <= new.from_dt
AND A1.id =
(
SELECT MAX( A2.id )
FROM A A2
WHERE A2.caseid = new.caseid
AND A2.as_cd = new.as_cd
AND A2.id < new.id
);
END
-------------------------------------------------------
Our insert operations are very slow after we put this trigger on table
A.
Application try to update THRU_DT of last row before inserting new row
with same caseid. and when it inserts a row after updating, trigger
fires.
Does trigger get updated value of THRU_DT from last row with same
caseid and same as_cd or it gets old value because application ( java
code ) did not commit this whole transaction yet?
Regards