473,847 Members | 1,720 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Determining same row in update trigger

Hello,

I'm having trouble solving the following problem with DB2 UDB 8.2.

I need to create a trigger that performs certain extra constraint
validations (temporal uniqueness). One of the tables has no primary
key.

I'm having trouble expressing an update trigger that checks for
existing rows, contemporary to the updated one, but exclusing the
updated one.

In effect the trigger always signals my message on updates, since the
updated row conflicts with itself.

CREATE TRIGGER un_ch_name_upda te
BEFORE UPDATE ON cmd$child
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.name in (select name
from cmd$child
where parent_sid = N.parent_sid
and (vts <= N.vte or n.vte is null)
and (vte n.vts or vte is null)))
SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='T he NAME is already present
in the time interval under the same PARENT_SID';

In oracle I could probably just have added "and rownum <n.rownum".
Any ideas?

TIA,

-dennis

Nov 17 '06 #1
13 6582
de****@geysirhe ste.dk wrote:
Hello,

I'm having trouble solving the following problem with DB2 UDB 8.2.

I need to create a trigger that performs certain extra constraint
validations (temporal uniqueness). One of the tables has no primary
key.

I'm having trouble expressing an update trigger that checks for
existing rows, contemporary to the updated one, but exclusing the
updated one.

In effect the trigger always signals my message on updates, since the
updated row conflicts with itself.

CREATE TRIGGER un_ch_name_upda te
BEFORE UPDATE ON cmd$child
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.name in (select name
from cmd$child
where parent_sid = N.parent_sid
and (vts <= N.vte or n.vte is null)
and (vte n.vts or vte is null)))
SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='T he NAME is already present
in the time interval under the same PARENT_SID';

In oracle I could probably just have added "and rownum <n.rownum".
Any ideas?
I believe that when I see it....

Anyway, I think your analysis is flawed. Since this is a BEFORE trigger
the update is not yet applied to the target table.
Even if it's a multi row UPDATE DB2 will ensure that the trigger will
fire for all rows on the pre-update image (AFAIK this is different than
Oracle which will always pipeline)

Can you post a script illustrating the problem?

Aside, a table without a primary key is asking for trouble. If all else
fails you can always add an IDENTITY column.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 17 '06 #2
Serge Rielau wrote:
I believe that when I see it....
Believe what?
Anyway, I think your analysis is flawed. Since this is a BEFORE trigger
the update is not yet applied to the target table.
I know, and it shouldn't be. But I need a way to find out whether
"conflictin g" rows already exist in the table. I do this with a "id not
in (select ...)", but this select will usually always include the
updated row, and thus make the row conflict with itself.
Can you post a script illustrating the problem?
I included the trigger in my original post. If I have the following in
the table:

name | parent_sid | vts | vte
foo | 1 | time1 | time2

and execute this statement

update table set parent_sid = 2 where parent_sid = 1

it would conflict with itself. I only need to see if there are *other*
rows within the same timespan with the same parent_sid.
Aside, a table without a primary key is asking for trouble. If all else
fails you can always add an IDENTITY column.
I know. This is a running production system, that we are trying to
retrofit with triggers for temporal consistency guarding. If it can at
all be avoided we don't want to add new columns, since that may break
existing INSERT statements with no columns defined.

I was just hoping for a rownum-like feature in DB2, but I guess there
is none...

Thanks,

-dennis

Nov 20 '06 #3
de****@geysirhe ste.dk wrote:
Serge Rielau wrote:
>I believe that when I see it....

Believe what?
That you can use rownum in the Oracle trigger the way you describe and
get what you want.
I find that unlikely.
>Anyway, I think your analysis is flawed. Since this is a BEFORE trigger
the update is not yet applied to the target table.

I know, and it shouldn't be. But I need a way to find out whether
"conflictin g" rows already exist in the table. I do this with a "id not
in (select ...)", but this select will usually always include the
updated row, and thus make the row conflict with itself.
>Can you post a script illustrating the problem?

I included the trigger in my original post. If I have the following in
the table:

name | parent_sid | vts | vte
foo | 1 | time1 | time2

and execute this statement

update table set parent_sid = 2 where parent_sid = 1

it would conflict with itself. I only need to see if there are *other*
rows within the same timespan with the same parent_sid.
Would?
db2 =CREATE TABLE cmd$child(name VARCHAR(10), parent_sid INT,
vts TIMESTAMP, vte TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =CREATE TRIGGER un_ch_name_upda te
db2 (cont.) =BEFORE UPDATE ON cmd$child
db2 (cont.) =REFERENCING NEW AS N
db2 (cont.) =FOR EACH ROW
db2 (cont.) =WHEN (N.name in (select name
db2 (cont.) = from cmd$child
db2 (cont.) = where parent_sid = N.parent_sid
db2 (cont.) = and (vts <= N.vte or n.vte is null)
db2 (cont.) = and (vte n.vts or vte is null)))
db2 (cont.) = SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='T he NAME is
already present';
DB20000I The SQL command completed successfully.
db2 =INSERT INTO cmd$child VALUES ('foo', 1, CURRENT TIMESTAMP - 1
DAY, CURREN
T TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =update cmd$child set parent_sid = 2 where parent_sid = 1;
DB20000I The SQL command completed successfully.
db2 =>

I was just hoping for a rownum-like feature in DB2, but I guess there
is none...
There is. It's called ROW_NUMBER() OVER() (and it also exists in Oracle
with the same name)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 20 '06 #4
Serge Rielau wrote:
de****@geysirhe ste.dk wrote:
>I was just hoping for a rownum-like feature in DB2, but I guess there
is none...

There is. It's called ROW_NUMBER() OVER() (and it also exists in Oracle
with the same name)
There's also the concept of a Row ID, which is effectively hidden from
SQL in most cases, though used internally by the database. iSeries folk
see this in other languages/access methods, and it wouldn't surprise me
if some databases do expose it in SQL. Perhaps that's what Dennis was
hoping for.
--g
Nov 20 '06 #5
Serge Rielau wrote:
That you can use rownum in the Oracle trigger the way you describe and
get what you want.
I find that unlikely.
Oh, I see. I haven't tried it, but thats what I thought rownum does in
oracle anyway. I realise that rownum and the like are things to avoid,
but I'm trying to solve a production issue here with minimal impact.
db2 =CREATE TABLE cmd$child(name VARCHAR(10), parent_sid INT,
vts TIMESTAMP, vte TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =CREATE TRIGGER un_ch_name_upda te
db2 (cont.) =BEFORE UPDATE ON cmd$child
db2 (cont.) =REFERENCING NEW AS N
db2 (cont.) =FOR EACH ROW
db2 (cont.) =WHEN (N.name in (select name
db2 (cont.) = from cmd$child
db2 (cont.) = where parent_sid = N.parent_sid
db2 (cont.) = and (vts <= N.vte or n.vte is null)
db2 (cont.) = and (vte n.vts or vte is null)))
db2 (cont.) = SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='T he NAME is
already present';
DB20000I The SQL command completed successfully.
db2 =INSERT INTO cmd$child VALUES ('foo', 1, CURRENT TIMESTAMP - 1
DAY, CURREN
T TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =update cmd$child set parent_sid = 2 where parent_sid = 1;
DB20000I The SQL command completed successfully.
db2 =>
Sorry - my constructed example wasn't very good (I tried to make it
simpler). I'll try with one that looks more like reality. What I'm
trying to do is avoid the same name re-used for the same parent_sid in
the sime timespan (vts being the beginning and vte being the end):

db2 =CREATE TABLE cmd$child(name VARCHAR(10), parent_sid INT,
child_sid INT,
vts TIMESTAMP, vte TIMESTAMP);
[Same trigger]
db2 =INSERT INTO cmd$child VALUES ('foo', 1, 1, CURRENT TIMESTAMP - 1

DAY, CURRENT TIMESTAMP);

db2 =update cmd$child set child_sid = 2 where child_sid = 1;
Thanks for helping out,

-dennis

Nov 20 '06 #6
Greg Nash wrote:
There's also the concept of a Row ID, which is effectively hidden from
SQL in most cases, though used internally by the database. iSeries folk
see this in other languages/access methods, and it wouldn't surprise me
if some databases do expose it in SQL. Perhaps that's what Dennis was
hoping for.
--g
Perhaps. That's called ROWID in Oracle though.
Anyway I can't repro the problem...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 20 '06 #7
de****@geysirhe ste.dk wrote:
Serge Rielau wrote:
>That you can use rownum in the Oracle trigger the way you describe and
get what you want.
I find that unlikely.

Oh, I see. I haven't tried it, but thats what I thought rownum does in
oracle anyway. I realise that rownum and the like are things to avoid,
but I'm trying to solve a production issue here with minimal impact.
>db2 =CREATE TABLE cmd$child(name VARCHAR(10), parent_sid INT,
vts TIMESTAMP, vte TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =CREATE TRIGGER un_ch_name_upda te
db2 (cont.) =BEFORE UPDATE ON cmd$child
db2 (cont.) =REFERENCING NEW AS N
db2 (cont.) =FOR EACH ROW
db2 (cont.) =WHEN (N.name in (select name
db2 (cont.) = from cmd$child
db2 (cont.) = where parent_sid = N.parent_sid
db2 (cont.) = and (vts <= N.vte or n.vte is null)
db2 (cont.) = and (vte n.vts or vte is null)))
db2 (cont.) = SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='T he NAME is
already present';
DB20000I The SQL command completed successfully.
db2 =INSERT INTO cmd$child VALUES ('foo', 1, CURRENT TIMESTAMP - 1
DAY, CURREN
T TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =update cmd$child set parent_sid = 2 where parent_sid = 1;
DB20000I The SQL command completed successfully.
db2 =>

Sorry - my constructed example wasn't very good (I tried to make it
simpler). I'll try with one that looks more like reality. What I'm
trying to do is avoid the same name re-used for the same parent_sid in
the sime timespan (vts being the beginning and vte being the end):

db2 =CREATE TABLE cmd$child(name VARCHAR(10), parent_sid INT,
child_sid INT,
vts TIMESTAMP, vte TIMESTAMP);
[Same trigger]
db2 =INSERT INTO cmd$child VALUES ('foo', 1, 1, CURRENT TIMESTAMP - 1

DAY, CURRENT TIMESTAMP);

db2 =update cmd$child set child_sid = 2 where child_sid = 1;
Still no failure?
Can you produce a complete example including the error text?

db2 =DROP TRIGGER un_ch_name_upda te;
DB20000I The SQL command completed successfully.
db2 =DROP TABLE cmd$child;
DB20000I The SQL command completed successfully.
db2 =CREATE TABLE cmd$child(name VARCHAR(10),
db2 (cont.) = parent_sid INT,
db2 (cont.) = child_sid INT,
db2 (cont.) = vts TIMESTAMP,
db2 (cont.) = vte TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =CREATE TRIGGER un_ch_name_upda te
db2 (cont.) =BEFORE UPDATE ON cmd$child
db2 (cont.) =REFERENCING NEW AS N
db2 (cont.) =FOR EACH ROW
db2 (cont.) =WHEN (N.name in (select name
db2 (cont.) = from cmd$child
db2 (cont.) = where parent_sid = N.parent_sid
db2 (cont.) = and (vts <= N.vte or n.vte is null)
db2 (cont.) = and (vte n.vts or vte is null)))
db2 (cont.) = SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='T he NAME is
already p
resent';
DB20000I The SQL command completed successfully.
db2 =>
db2 =INSERT INTO cmd$child VALUES ('foo', 1, 1, CURRENT TIMESTAMP - 1
DAY, CUR
RENT TIMESTAMP);
DB20000I The SQL command completed successfully.
db2 =update cmd$child set parent_sid = 2 where parent_sid = 1;
DB20000I The SQL command completed successfully.
db2 =>

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 20 '06 #8
Serge Rielau wrote:
Still no failure?
Can you produce a complete example including the error text?
[snip]
db2 =update cmd$child set parent_sid = 2 where parent_sid = 1;
DB20000I The SQL command completed successfully.
That last update should actually be on something else than parent_sid,
since that is the "parent" for which the name must not be duplicated
for overlapping intervals. For instance the update should change_sid.

The table describes a temporal hierarchy with parents and children that
have names and exist for a specific period of time or from some time
until forever (vte=null).

Regarding row_number() over() : Does it always guarantee the same
ordering, even for duplicates? As I mentioned there is no primary key
and nothing that's guaranteed unique.

-dennis

Nov 20 '06 #9
Ahhh! Dennis,

please cut the "should" and the "would".
If you claim something doesn't work please back it up with a runnable
example that readers of your post can use to duplicate your problem.
Otherwise we can't give you advise.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 20 '06 #10

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

Similar topics

8
8618
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is the primary exchange for that symbol. Each symbol can only have one primary exchange. I am trying to write a insert/update/delete trigger that enforces this rule. The rules I have thought of are as follows: Insert If new row has flag...
1
6181
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement inside the if followed by an insert to the audit table. When I leave only a few IF-s in the...
1
15432
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
3
7287
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the values that are passed in via the insert trigger without having to use all the 'set' statements for each field (so if we add fields in the future I won't have to update the trigger). In other words, I want the trigger code to look something like...
3
1341
by: Martijn van Oosterhout | last post by:
I was just trying to find a query what would determine, given a username, what tables they can see and what permissions they have on those tables. Obviously this would only work for superusers, but does anyone have any ideas? A while ago someone tried using the like operator, would that be the best approach? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even...
2
6569
by: D. Dante Lorenso | last post by:
I'm trying to build a table that will store a history of records by enumerating the records. I want the newest record to always be number ZERO, so I created a trigger on my table to handle the assignment of version numbers: CREATE TRIGGER "trg_audio_file_insert" BEFORE INSERT ON "public"."audio_file" FOR EACH ROW EXECUTE PROCEDURE "public"."trg_audio_file_insert"(); My trigger function looks like this...
5
5365
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table schema1.emp ( fname varchar(15) not null, lname varchar(15) not null, dob date,
1
6216
by: abhi81 | last post by:
Hello All, I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp. Insert and Update trigger work fine when i have only one of them defined. However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and...
13
4180
by: Neil | last post by:
I'm running an update query in SQL 7 from QA, and it runs forever. Has been running for 20 minutes so far! The query is quite simple: update a single field in a table, based on a join with another table. There are a total of 1200 rows that are affected (out of a total of 60,000 in the table). Should not take 20 minutes! Also, retrieving the 1200 rows using the same criteria is instantaneous. Anyone have any idea what's going on? Here's...
0
9882
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9727
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10982
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10645
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10335
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9481
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5719
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4524
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3160
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.