SEO

#1 (**permalink**) 02-27-2008, 07:11 AM

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 :-)

#2 (**permalink**) 02-27-2008, 07:11 AM

rmcgorman@gmail.com wrote:
> 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
Well, give us the trigger in T-SQL and we can walk you through the
translation into ANSI SQL.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#3 (**permalink**) 02-27-2008, 07:11 AM

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

rmcgorman@gmail.com wrote:
> 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 :-)
>

#4 (**permalink**) 02-27-2008, 07:11 AM

rmcgorman@gmail.com wrote:
> 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 :-)
>
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

#5 (**permalink**) 02-27-2008, 07:11 AM

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

#6 (**permalink**) 02-27-2008, 07:11 AM

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.
;-)

#7 (**permalink**) 02-27-2008, 07:11 AM

rmcgorman@gmail.com wrote:
> 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
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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Newbie Insert Trigger question