Thread: Help on triggers

Help on triggers

From
"Pradeepkumar, Pyatalo (IE10)"
Date:
Hi all,

I have 2 tables - table1 and table2. Both the tables are related to each
other. I have written triggers so that if a tuple is inserted into table1,
corresponding values will be inserted into table2. and if a tuple is deleted
from table1 then corresponding tuples are deleted from table2 also. The
delete trigger works fine sometimes and other times it gives the following
error.....

delete from table1 where pointid=30;
ERROR: update or delete on "table1" violates foreign key constraint "$2" on
"table2"
DETAIL: key (pointid) =30 is still referenced from table "pointattributes".

I am doing an application in C. I have written a C-function using libpq API
to delete a tuple from table1. that is I pass a parameter to the function
and it has to delete the tuple whose primarykey value= parameter and also
delete correspoding tuples from table2(due to the trigger). so i have formed
a simple delete command on table2in the C-function. When i compile the
application it gives the same error:

is there anyone who can explain why this is happening. I had posted this
query 2 days back...dint get any reply. Please help me out with this.



> With Best Regards
> Pradeep Kumar P J
>

Re: Help on triggers

From
"Scott Marlowe"
Date:
On Sun, 2004-08-01 at 23:27, Pradeepkumar, Pyatalo (IE10) wrote:
> Hi all,
>
> I have 2 tables - table1 and table2. Both the tables are related to each
> other. I have written triggers so that if a tuple is inserted into table1,
> corresponding values will be inserted into table2. and if a tuple is deleted
> from table1 then corresponding tuples are deleted from table2 also. The
> delete trigger works fine sometimes and other times it gives the following
> error.....
>
> delete from table1 where pointid=30;
> ERROR: update or delete on "table1" violates foreign key constraint "$2" on
> "table2"
> DETAIL: key (pointid) =30 is still referenced from table "pointattributes".
>
> I am doing an application in C. I have written a C-function using libpq API
> to delete a tuple from table1. that is I pass a parameter to the function
> and it has to delete the tuple whose primarykey value= parameter and also
> delete correspoding tuples from table2(due to the trigger). so i have formed
> a simple delete command on table2in the C-function. When i compile the
> application it gives the same error:
>
> is there anyone who can explain why this is happening. I had posted this
> query 2 days back...dint get any reply. Please help me out with this.

Using foreign keys, you should be able to just set the relation to
cascade and have the dependent rows deleted automagically.


Re: Help on triggers

From
"Pradeepkumar, Pyatalo (IE10)"
Date:
I have done that. while creating table2 i have specified ON DELETE CASCADE
for the foreign key constraint.
But I am getting this error when i call the function from a C++ file.



-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@qwest.net]
Sent: Monday, August 02, 2004 1:49 PM
To: Pradeepkumar, Pyatalo (IE10)
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Help on triggers


On Sun, 2004-08-01 at 23:27, Pradeepkumar, Pyatalo (IE10) wrote:
> Hi all,
>
> I have 2 tables - table1 and table2. Both the tables are related to each
> other. I have written triggers so that if a tuple is inserted into table1,
> corresponding values will be inserted into table2. and if a tuple is
deleted
> from table1 then corresponding tuples are deleted from table2 also. The
> delete trigger works fine sometimes and other times it gives the following
> error.....
>
> delete from table1 where pointid=30;
> ERROR: update or delete on "table1" violates foreign key constraint "$2"
on
> "table2"
> DETAIL: key (pointid) =30 is still referenced from table
"pointattributes".
>
> I am doing an application in C. I have written a C-function using libpq
API
> to delete a tuple from table1. that is I pass a parameter to the function
> and it has to delete the tuple whose primarykey value= parameter and also
> delete correspoding tuples from table2(due to the trigger). so i have
formed
> a simple delete command on table2in the C-function. When i compile the
> application it gives the same error:
>
> is there anyone who can explain why this is happening. I had posted this
> query 2 days back...dint get any reply. Please help me out with this.

Using foreign keys, you should be able to just set the relation to
cascade and have the dependent rows deleted automagically.

Re: Help on triggers

From
"Scott Marlowe"
Date:
Are you then still trying to delete the dependent row in your function?
That wouldn't make any sense, since the cascading fk should delete it
anyway.

On Mon, 2004-08-02 at 03:00, Pradeepkumar, Pyatalo (IE10) wrote:
> I have done that. while creating table2 i have specified ON DELETE CASCADE
> for the foreign key constraint.
> But I am getting this error when i call the function from a C++ file.
>
>
>
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@qwest.net]
> Sent: Monday, August 02, 2004 1:49 PM
> To: Pradeepkumar, Pyatalo (IE10)
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Help on triggers
>
>
> On Sun, 2004-08-01 at 23:27, Pradeepkumar, Pyatalo (IE10) wrote:
> > Hi all,
> >
> > I have 2 tables - table1 and table2. Both the tables are related to each
> > other. I have written triggers so that if a tuple is inserted into table1,
> > corresponding values will be inserted into table2. and if a tuple is
> deleted
> > from table1 then corresponding tuples are deleted from table2 also. The
> > delete trigger works fine sometimes and other times it gives the following
> > error.....
> >
> > delete from table1 where pointid=30;
> > ERROR: update or delete on "table1" violates foreign key constraint "$2"
> on
> > "table2"
> > DETAIL: key (pointid) =30 is still referenced from table
> "pointattributes".
> >
> > I am doing an application in C. I have written a C-function using libpq
> API
> > to delete a tuple from table1. that is I pass a parameter to the function
> > and it has to delete the tuple whose primarykey value= parameter and also
> > delete correspoding tuples from table2(due to the trigger). so i have
> formed
> > a simple delete command on table2in the C-function. When i compile the
> > application it gives the same error:
> >
> > is there anyone who can explain why this is happening. I had posted this
> > query 2 days back...dint get any reply. Please help me out with this.
>
> Using foreign keys, you should be able to just set the relation to
> cascade and have the dependent rows deleted automagically.
>