By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,188 Members | 832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,188 IT Pros & Developers. It's quick & easy.

Determining same row in update trigger

P: n/a
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_update
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='The 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
Share this Question
Share on Google+
13 Replies


P: n/a
de****@geysirheste.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_update
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='The 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

P: n/a
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
"conflicting" 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

P: n/a
de****@geysirheste.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
"conflicting" 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_update
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='The 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

P: n/a
Serge Rielau wrote:
de****@geysirheste.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

P: n/a
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_update
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='The 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

P: n/a
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

P: n/a
de****@geysirheste.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_update
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='The 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_update;
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_update
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='The 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

P: n/a
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

P: n/a
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

P: n/a
Serge Rielau wrote:
please cut the "should" and the "would".
How come?
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.
I'm not claiming anything doesn't work. I'm merely asking for help
solving a problem with expressing a trigger, so that it does what I
need...
Otherwise we can't give you advise.
Advise is what I really would (and and do) appreciate. This example
should reproduce the problem (with the trigger - not db2) I'm seeing:

DROP TRIGGER un_ch_name_update;

DROP TABLE cmd$child;

CREATE TABLE cmd$child(name VARCHAR(10),
parent_sid INT,
child_sid INT,
vts TIMESTAMP,
vte TIMESTAMP);

CREATE TRIGGER un_ch_name_update
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='The NAME is already
present';

INSERT INTO cmd$child VALUES ('foo', 1, 1, CURRENT TIMESTAMP - 1
DAY, CURRENT TIMESTAMP);

update cmd$child set child_sid = 2 where child_sid = 1;
We've touched on row_number() over() but does that always guarantee the
ordering - even for duplicate rows? And can I use it with the "new" row
within the trigger? I haven't been able to find any documentation on
this.

Would an IDENTITY column fill in unique values automatically when added
to a table? Perhaps that's the only possible solution, even though I'd
like to avoid adding a column.

-dennis

Nov 21 '06 #11

P: n/a
de****@geysirheste.dk wrote:
DROP TRIGGER un_ch_name_update;

DROP TABLE cmd$child;

CREATE TABLE cmd$child(name VARCHAR(10),
parent_sid INT,
child_sid INT,
vts TIMESTAMP,
vte TIMESTAMP);

CREATE TRIGGER un_ch_name_update
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='The NAME is already
present';

INSERT INTO cmd$child VALUES ('foo', 1, 1, CURRENT TIMESTAMP - 1
DAY, CURRENT TIMESTAMP);

update cmd$child set child_sid = 2 where child_sid = 1;
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "The NAME is
already
present". SQLSTATE=75000

Good.. Now we have something to talk about.

1. There is no need to fire the trigger unless one of the columns you
care about is modified, so by adding a column list performance will be
improved and the problem is limited
2. You do have a means to uniquely identify any row:
If no two (name, parent_sid) combinations can ever overlap for a
given time interval that implied that they sure can't be identical. That
means that:
(name, parent_sid, vts) and (name, parent_sid, vte) are both unique keys
I don't know your rules for NULLs, so they may not be legal primary
keys. Anyway it is possible to identify the original row by any of those
two combinations. Hence excluding that row will fix your problem.
I checked both vts and vte which is overkill, maybe not all null checks
are needed either.

CREATE TRIGGER un_ch_name_update
BEFORE UPDATE OF name, parent_sid, vts, vte ON cmd$child
REFERENCING NEW AS N OLD AS O
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)
and not (o.vte = vte or (o.vte IS NULL and vte IS NULL))
and not (o.vts = vts or (o.vte IS NULL and vte IS NULL))
and not (o.parent_sid = parent_sid or (o.parent_sid
IS NULL and parent_sid IS NULL))
and not (o.name = name or (o.name IS NULL and name
IS NULL))))
SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='The NAME is already present';
>

We've touched on row_number() over() but does that always guarantee the
ordering - even for duplicate rows? And can I use it with the "new" row
within the trigger? I haven't been able to find any documentation on
this.
You would need to add an order in the OVER() clause to guarantee
ordering. See above you do have two unique keys as far as I can see.
>
Would an IDENTITY column fill in unique values automatically when added
to a table? Perhaps that's the only possible solution, even though I'd
like to avoid adding a column.
Yes, an IDENTITY column can do that. Simply omit it from the INSERT
column list or use the DFEAULT keyword for a value.
Don't forget to add a primary key for the column. DB2 will NOT add the
constraint on it's own because by altering or cycling an identity column
it can provoke dups.

Hope that helps
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

P: n/a
Serge Rielau wrote:
1. There is no need to fire the trigger unless one of the columns you
care about is modified, so by adding a column list performance will be
improved and the problem is limited
Thats a good optimization...
2. You do have a means to uniquely identify any row:
If no two (name, parent_sid) combinations can ever overlap for a
given time interval that implied that they sure can't be identical. That
means that:
(name, parent_sid, vts) and (name, parent_sid, vte) are both unique keys
But nothing enforces this. So unfortunately faulty software can insert
duplicates. This trigger needs to be added to a running production
system with plenty of data.
Would an IDENTITY column fill in unique values automatically when added
to a table? Perhaps that's the only possible solution, even though I'd
like to avoid adding a column.
Yes, an IDENTITY column can do that. Simply omit it from the INSERT
I meant when added the column to an existing table with existing rows,
but I found out that I can't add an identity column to an existing
table at all.

Therefore it looks like the solution will be adding an IDENTITY primary
key (creating a new table, inserting existing data, renaming table).

Thanks for the effort,

-dennis

Nov 22 '06 #13

P: n/a
qa****@gmail.com wrote:
>2. You do have a means to uniquely identify any row:
If no two (name, parent_sid) combinations can ever overlap for a
given time interval that implied that they sure can't be identical. That
means that:
(name, parent_sid, vts) and (name, parent_sid, vte) are both unique keys
But nothing enforces this. So unfortunately faulty software can insert
duplicates. This trigger needs to be added to a running production
system with plenty of data.
You COULD enforce it. Your trigger (I assume you will have a similar one
for INSERT!) will also prevent bad rows from being inserted.
If you have bad data in you table you're in trouble anyway.
>>Would an IDENTITY column fill in unique values automatically when added
to a table? Perhaps that's the only possible solution, even though I'd
like to avoid adding a column.
Yes, an IDENTITY column can do that. Simply omit it from the INSERT

I meant when added the column to an existing table with existing rows,
but I found out that I can't add an identity column to an existing
table at all.
Therefore it looks like the solution will be adding an IDENTITY primary
key (creating a new table, inserting existing data, renaming table).
Be creative! DB2 does not allow you to add a column with an identity
column because it doesn't know what you want to happen for existing
rows. Tell it:
ALTER TABLE T ADD COLUMN ID INTEGER NOT NULL WITH DEFAULT; -- Start
existing rows of with 0
ALTER TABLE T ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY; -- might
have the syntax wrong here.. please look it up
UPDATE T SET ID = DEFAULT; -- Make sure you got enough logs or do it in
chunks
ALTER TABLE T ADD CONSTRAINT PK PRIMARY KEY(ID)
I'm a bit confused... Have you tried the trigger? It will do what you want.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

This discussion thread is closed

Replies have been disabled for this discussion.