Updating restrictions on insert triggers
This is a multi-part message in MIME format.
------=_NextPart_000_002B_01BDF113.EB2931A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all,
I have the following situation:
create table A
(
A1 integer NON NULL PRIMARY KEY,
A2 integer
);
create table B
(
B1 integer NOT NULL PRIMARY KEY,
A1 integer NOT NULL FOREIGN KEY REFERENCES A(A1),
A2 integer
);Now, the A2 in B is a denormalization that I need in order to improve =
performance, and is derived from the A2 in A. I need to write =
insert/update triggers to populate A2 in B. The query in the trigger =
should be:
update Bset A2 =3D( select A2 from A where A.A1 =3D =
inserted.A1)where B.B1 =3D inserted.B1
This thing works great in the update trigger as long as I make sure that =
A2 is not present in the column list for the trigger (it would throw an =
exception otherwise). However, this does not work in the insert =
trigger. I can create the trigger just fine, but the inserts fail with =
an error stating that 'the table or column being updated is present in =
the triggering statement' or something like that.
Is there a way around this? Or is it not possible at all to 'pre/post =
process' the rows inserted?
Please CC replies to mailto:vipul@healtheon.com=20
-------------------------------------------------------------------------=
-------
Vipul=20
------=_NextPart_000_002B_01BDF113.EB2931A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>
<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.3509.100"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>
<P><FONT color=3D#000000 size=3D2>Hi all,</FONT></P>
<P><FONT size=3D2>I have the following situation:</FONT></P><PRE>create =
table A
(
A1 integer NON NULL PRIMARY KEY,
A2 integer
);</PRE><PRE>create table B
(
B1 integer NOT NULL PRIMARY KEY,
A1 integer NOT NULL FOREIGN KEY REFERENCES A(A1),
A2 integer
);</PRE>
<P><FONT size=3D2>Now, the A2 in B is a denormalization that I need in =
order to=20
improve performance, and is derived from the A2 in A. I need to =
write=20
insert/update triggers to populate A2 in B. The query in the =
trigger=20
should be:</FONT></P><PRE>update B<BR>set A2 =
=3D<BR>(<BR> select A2<BR> from =
A<BR> where A.A1 =3D inserted.A1<BR>)<BR>where B.B1 =
=3D inserted.B1</PRE>
<P><FONT size=3D2>This thing works great in the update trigger as long =
as I make=20
sure that A2 is not present in the column list for the trigger (it would =
throw=20
an exception otherwise). However, this does not work in the insert =
trigger. I can create the trigger just fine, but the inserts fail =
with an=20
error stating that 'the table or column being updated is present in the=20
triggering statement' or something like that.</FONT></P>
<P><FONT size=3D2>Is there a way around this? Or is it not =
possible at all=20
to 'pre/post process' the rows inserted?</FONT></P>
<P><FONT size=3D2>Please CC replies to <A=20
href=3D"mailto:vipul@healtheon.com">mailto:vipul@healtheon.com</A> </P>
<HR>
Vipul=20
<P> </P></FONT></DIV></BODY></HTML>
------=_NextPart_000_002B_01BDF113.EB2931A0--