473,394 Members | 1,195 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

AFTER INSERT ON TRIGGER

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

Apr 16 '07 #1
1 8956
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
Apr 17 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ford Desperado | last post by:
I've been reading the docs and playing around, but I'm still not getting the difference. For instance, create table a(i int check(i>0)) create table a_src(i int) go create unique index ai on...
0
by: Bill Smith | last post by:
I am looking for an example 'after insert' trigger that performs an update to a column in the master table. For example, I have an 'orders' table that contains a 'PartNum' column. I would like to...
3
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code...
1
by: ibrettferguson | last post by:
Nothing fancy; just a trigger on a sharepoint table that supposed to write a record to another SQL table. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER ON .
2
by: mayankaswal | last post by:
I am trying to create a AFTER INSERT Trigger. I created it and it worked for one record. But when I am trying to input multiple inserts it gives me error even on my insert statement. I am sure the...
2
by: wugon.net | last post by:
Problem: after inser trigger encounter error sql0348 Env:db2 v8 + fp 13 + win xp Description: we build two after insert triggers DB2.TRG1, DB2.TRG2 on base table DB2.TEST1, insert data into...
1
by: Stout | last post by:
Is it possible to create a trigger in one database, that after an insert, will update a database on a different server? If so, how would I do this? Thanks. Bill
2
by: gimme_this_gimme_that | last post by:
I'm using DB2 8.1. Suppose table foo has columns name and lname: create table foo (name as varchar(200), lname as varchar(200)); Write a trigger that inserts the lower case value of name...
1
by: ravishna | last post by:
My question is After inserting a row,I need to update some of the columns.How can I do with the after insert trigger. See the codings below . CREATE OR REPLACE TRIGGER order_id_trg_upd after...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.