This is a multipart message in MIME format.
------=_NextPart_000_0016_01C89E2D.B4D5B340
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
I would like to create a rule that, by updating a view, allows me to
update
one table and insert into another.
The following example illustrates what I'm trying to do:
--Create Tables
CREATE TABLE my_table
(
my_table_id serial,
a character varying(255),
b character varying(255),
CONSTRAINT my_table_id_pk PRIMARY KEY (my_table_id)
);
CREATE TABLE my_audit_table
(
audit_id serial,
my_table_id int,
c character varying(255),
CONSTRAINT audit_id_pk PRIMARY KEY (audit_id)
);
--Create View
CREATE OR REPLACE VIEW my_view AS
SELECT
t.my_table_id,
t.a,
t.b,
au.audit_id,
au.c
FROM
my_table t, my_audit_table au
WHERE
t.my_table_id = au.my_table_id;
--Create Rules
CREATE OR REPLACE RULE insert_to_my_view AS
ON INSERT TO my_view
DO INSTEAD(
INSERT INTO my_table (a,b)
VALUES(new.a, new.b);
INSERT INTO my_audit_table(my_table_id, c)
VALUES
(currval('my_table_my_table_id_seq'), new.c);
);
CREATE OR REPLACE RULE update_my_view AS
ON UPDATE TO my_view DO INSTEAD
( UPDATE my_table SET
a = new.a,
b = new.b
WHERE
my_table_id = old.my_table_id;
INSERT INTO my_audit_table
(my_table_id,
c)
VALUES
(new.my_table_id,
new.c);
);
--The insert statement below inserts one row into my_table, and one row
into
my_audit_table
--(This works the way I would like)
insert into my_view(a,b,c) values('a contents','b contents', 'c
contents');
--The update statement below doesn't work the way I want.
--What I would like this to do is to update one row in my_table, and
insert
--one row into my_audit table. It does the update fine, but the insert to
my_audit_table
--doesn't work as I had anticipated.
update my_view set a = 'new a contents', b = 'new b contents', c = 'new c
contents' where my_table_id = 1;
If I execute the above update statement multiple times, multiple rows will
be
inserted with each call after the first call.
Specifically,
.. after the first call, 1 row is inserted
.. after the second call, 2 rows are inserted
.. after the third call, 4 rows are inserted
.. after the fourth call, 8 rows are inserted... and so on
The problem is due to the INSERT in the update_my_view rule:
INSERT INTO my_audit_table
(my_table_id,
c)
VALUES
(new.my_table_id,
new.c);
Apparently, "new.my_table_id" in this case references more than one row,
if
more than one row with
the given id already exists in my_audit_table.
How do I accomplish what I want to accomplish here? I'd prefer not to use
a
sp.
Thanks,
Chad
------=_NextPart_000_0016_01C89E2D.B4D5B340
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml"
=
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@[EMAIL PROTECTED]
0 0 0 0 0 0 0 0 0;}
@[EMAIL PROTECTED]
"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@[EMAIL PROTECTED]
15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
..MsoChpDefault
{mso-style-type:ex****t-only;}
@[EMAIL PROTECTED]
Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
/* List Definitions */
@[EMAIL PROTECTED]
l0
{mso-list-id:229657649;
mso-list-type:hybrid;
mso-list-template-ids:1741213152 67698689 67698691 67698693 67698689 =
67698691 67698693 67698689 67698691 67698693;}
@[EMAIL PROTECTED]
l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal>I would like to create a rule that, by updating a =
view,
allows me to update one table and insert into another.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>The following example illustrates what I’m =
trying to
do:<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>--Create Tables<o:p></o:p></p>
<p class=3DMsoNormal>CREATE TABLE my_table <o:p></o:p></p>
<p class=3DMsoNormal>(<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
my_table_id serial,<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
a character varying(255),<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
b character varying(255),<o:p></o:p></p>
<p class=3DMsoNormal>CONSTRAINT my_table_id_pk PRIMARY KEY =
(my_table_id)<o:p></o:p></p>
<p class=3DMsoNormal>);<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>CREATE TABLE my_audit_table<o:p></o:p></p>
<p class=3DMsoNormal>(<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
audit_id serial,<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
my_table_id int,<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
c character varying(255),<o:p></o:p></p>
<p class=3DMsoNormal>CONSTRAINT audit_id_pk PRIMARY KEY =
(audit_id)<o:p></o:p></p>
<p class=3DMsoNormal>);<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>--Create View<o:p></o:p></p>
<p class=3DMsoNormal>CREATE OR REPLACE VIEW my_view AS<o:p></o:p></p>
<p class=3DMsoNormal>SELECT
&=
nbsp;
<o:p></o:p></p>
<p class=3DMsoNormal>t.my_table_id,<o:p></o:p></p>
<p class=3DMsoNormal>t.a,<o:p></o:p></p>
<p class=3DMsoNormal>t.b,<o:p></o:p></p>
<p class=3DMsoNormal>au.audit_id,<o:p></o:p></p>
<p class=3DMsoNormal>au.c<o:p></o:p></p>
<p class=3DMsoNormal>FROM<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
my_table t, my_audit_table au<o:p></o:p></p>
<p class=3DMsoNormal>WHERE<o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
t.my_table_id =3D au.my_table_id;<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>--Create Rules<o:p></o:p></p>
<p class=3DMsoNormal>CREATE OR REPLACE RULE insert_to_my_view AS =
<o:p></o:p></p>
<p class=3DMsoNormal>ON INSERT TO my_view <o:p></o:p></p>
<p class=3DMsoNormal>DO INSTEAD( <o:p></o:p></p>
<p class=3DMsoNormal>INSERT INTO my_table (a,b)<o:p></o:p></p>
<p class=3DMsoNormal>VALUES(new.a, new.b); <o:p></o:p></p>
<p class=3DMsoNormal>INSERT INTO my_audit_table(my_table_id, c) =
<o:p></o:p></p>
<p class=3DMsoNormal>VALUES <o:p></o:p></p>
<p class=3DMsoNormal>(currval('my_table_my_table_id_seq'), new.c); =
<o:p></o:p></p>
<p class=3DMsoNormal>);<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>CREATE OR REPLACE RULE update_my_view AS =
<o:p></o:p></p>
<p class=3DMsoNormal>ON UPDATE TO my_view DO INSTEAD =
<o:p></o:p></p>
<p class=3DMsoNormal> ( UPDATE my_table SET <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
a =3D new.a, <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
b =3D new.b <o:p></o:p></p>
<p class=3DMsoNormal>WHERE <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
my_table_id =3D old.my_table_id; <o:p></o:p></p>
<p class=3DMsoNormal>INSERT INTO my_audit_table <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
(my_table_id, <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
c) <o:p></o:p></p>
<p class=3DMsoNormal>VALUES <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
(new.my_table_id, <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
new.c); <o:p></o:p></p>
<p class=3DMsoNormal>);<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>--The insert statement below inserts one row into =
my_table,
and one row into my_audit_table<o:p></o:p></p>
<p class=3DMsoNormal>--(This works the way I would like)<o:p></o:p></p>
<p class=3DMsoNormal>insert into my_view(a,b,c) values('a contents','b =
contents',
'c contents');<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>--The update statement below doesn’t work the =
way I
want.<o:p></o:p></p>
<p class=3DMsoNormal>--What I would like this to do is to update one row =
in
my_table, and insert<o:p></o:p></p>
<p class=3DMsoNormal>--one row into my_audit table. It does the =
update
fine, but the insert to my_audit_table<o:p></o:p></p>
<p class=3DMsoNormal>--doesn't work as I had anticipated. =
<o:p></o:p></p>
<p class=3DMsoNormal>update my_view set a =3D 'new a contents', b =3D =
'new b
contents', c =3D 'new c contents' where my_table_id =3D =
1;<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>If I execute the above update statement multiple =
times,
multiple rows will be <o:p></o:p></p>
<p class=3DMsoNormal>inserted with each call after the first =
call.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Specifically,<o:p></o:p></p>
<p class=3DMsoListParagraph style=3D'text-indent:-.25in;mso-list:l0 =
level1 lfo1'><![if !sup****tLists]><span
style=3D'font-family:Symbol'><span =
style=3D'mso-list:Ignore'>·<span
style=3D'font:7.0pt "Times New =
Roman"'>
</span></span></span><![endif]>after the first call, 1 row is =
inserted<o:p></o:p></p>
<p class=3DMsoListParagraph style=3D'text-indent:-.25in;mso-list:l0 =
level1 lfo1'><![if !sup****tLists]><span
style=3D'font-family:Symbol'><span =
style=3D'mso-list:Ignore'>·<span
style=3D'font:7.0pt "Times New =
Roman"'>
</span></span></span><![endif]>after the second call, 2 rows are =
inserted<o:p></o:p></p>
<p class=3DMsoListParagraph style=3D'text-indent:-.25in;mso-list:l0 =
level1 lfo1'><![if !sup****tLists]><span
style=3D'font-family:Symbol'><span =
style=3D'mso-list:Ignore'>·<span
style=3D'font:7.0pt "Times New =
Roman"'>
</span></span></span><![endif]>after the third call, 4 rows are =
inserted<o:p></o:p></p>
<p class=3DMsoListParagraph style=3D'text-indent:-.25in;mso-list:l0 =
level1 lfo1'><![if !sup****tLists]><span
style=3D'font-family:Symbol'><span =
style=3D'mso-list:Ignore'>·<span
style=3D'font:7.0pt "Times New =
Roman"'>
</span></span></span><![endif]>after the fourth call, 8 rows are =
inserted...
and so on<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>The problem is due to the INSERT in the =
update_my_view rule:<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>INSERT INTO my_audit_table <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
(my_table_id, <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
c) <o:p></o:p></p>
<p class=3DMsoNormal>VALUES <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
(new.my_table_id, <o:p></o:p></p>
<p =
class=3DMsoNormal> &=
nbsp;
new.c);<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Apparently, "new.my_table_id" in this =
case
references more than one row, if more than one row with <o:p></o:p></p>
<p class=3DMsoNormal>the given id already exists in =
my_audit_table.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>How do I accomplish what I want to accomplish =
here?
I'd prefer not to use a sp.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Thanks,<o:p></o:p></p>
<p class=3DMsoNormal>Chad<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
</div>
</body>
</html>
------=_NextPart_000_0016_01C89E2D.B4D5B340--
|
