Newbie Insert Trigger question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rmcgorman@gmail.com - 29 May 2006 19:28 GMT

I've been a Sybase DBA for a number of years. We now have a number of
DB2 servers that
I'm starting to work on. I've written many Sybase triggers but the DB2
syntax is still unfamiliar. :-(

I have a table AAA with columns "a" character(6), "b" character (30),
and some others.

I have a table LLL with columns "a" character(6), "b" character (30)

When I insert into table AAA I do not know the value of column "b". I
need my insert trigger to lookup the record in LLL where LLL.a = AAA.a,
and insert the corresponding value from LLL.b into AAA.b

TIA :-)

Reply to this Message

Serge Rielau - 29 May 2006 21:23 GMT

> I've been a Sybase DBA for a number of years. We now have a number of
> DB2 servers that
[quoted text clipped - 9 lines]
> need my insert trigger to lookup the record in LLL where LLL.a = AAA.a,
> and insert the corresponding value from LLL.b into AAA.b

Well, give us the trigger in T-SQL and we can walk you through the
translation into ANSI SQL.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

rmcgorman@gmail.com - 30 May 2006 14:47 GMT

Fair enough. :-)

create trigger AAA_ins_trg
on AAA for insert as

update AAA
set bbb = L.bbb
from AAA A,
LLL L
where A.aaa = L.aaa

return

Reply to this Message

Serge Rielau - 30 May 2006 15:51 GMT

> Fair enough. :-)
>
[quoted text clipped - 8 lines]
>
> return

Huh? Why don't you refer to the changed rowset at all?

CREATE TRIGGER AAA_ins_trg
AFTER INSERT ON AAA FOR EACH STATEMENT
MERGE INTO AAA USING LLL L ON A.aaa = L.aaa
WHEN MATCHED THEN A.bbb = L.bbb

(Makes no sense to me....)

Could it be that LLL is the "INSERTED" table?

In this case:

CREATE TRIGGER AAA_ins_trg
BEFORE INSERT ON AAA REFERENCING NEW AS n FOR EACH ROW
SET n.bbb = .....

Cheers
Serge
Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Phil Sherman - 30 May 2006 07:02 GMT

The trigger is relatively simple but I'd like to know why you want to do
this. Table AAA, containing column "b" should violate either first or
second normal form because column "b" appears to be dependent on the
value of column "a". (Which rule is violated depends on the key of AAA.)
If column "b" in AAA is a significant fraction of the row length, then
its effect on the table size could impact retrieval performance. It will
definitely effect update performance or cause data integrity issues if
the value of column "b" in the LLL table ever changes for a row.

Philip Sherman

> I've been a Sybase DBA for a number of years. We now have a number of
> DB2 servers that
[quoted text clipped - 11 lines]
>
> TIA :-)

Reply to this Message

rmcgorman@gmail.com - 30 May 2006 14:54 GMT

I'm stuck with a bad 3rd-party data model from a project three years
ago. We had talked about fixing the model during a recent upgrade
but the project timeline precluded that. :-( Welcome to my world.
;-)

Reply to this Message

Art S. Kagel - 30 May 2006 14:04 GMT

> I've been a Sybase DBA for a number of years. We now have a number of
> DB2 servers that
[quoted text clipped - 11 lines]
>
> TIA :-)

I agree with Phil and would add, if you need to always retrieve 'b' then
create an uninstantiated VIEW that includes the join from AAA to LLL on 'a'.
Problem solved without the insert overhead of the trigger or the
duplicated and possibility of column 'b' in AAA becoming out of synch with
LLL over time.

Art S. Kagel

Reply to this Message