Determining same row in update trigger

[dennis@geysirheste.dk]

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

[Serge Rielau]

Re: Determining same row in update trigger

dennis@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/Forum2006/Forum2006.html

[dennis@geysirheste.dk]

Re: Determining same row in update trigger

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

[Serge Rielau]

Re: Determining same row in update trigger

dennis@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/Forum2006/Forum2006.html

[Greg Nash]

Re: Determining same row in update trigger

Serge Rielau wrote:

dennis@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

[dennis@geysirheste.dk]

Re: Determining same row in update trigger

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

[Serge Rielau]

Re: Determining same row in update trigger

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/Forum2006/Forum2006.html

[Serge Rielau]

Re: Determining same row in update trigger

dennis@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/Forum2006/Forum2006.html

[dennis@geysirheste.dk]

Re: Determining same row in update trigger

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

[Serge Rielau]

Re: Determining same row in update trigger

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/Forum2006/Forum2006.html

[dennis@geysirheste.dk]

Re: Determining same row in update trigger

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_upda te;

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_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';

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

[Serge Rielau]

Re: Determining same row in update trigger

dennis@geysirhe ste.dk wrote:

DROP TRIGGER un_ch_name_upda te;
>
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_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';
>
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_upda te
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='T he 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/Forum2006/Forum2006.html

[qabi01@gmail.com]

Re: Determining same row in update trigger

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

[Serge Rielau]

Re: Determining same row in update trigger

qabi01@gmail.co m 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/Forum2006/Forum2006.html