Thread: help on delete trigger.
I am developing a db application in postgresql and i need to write a delete trigger on one of the tables.
the environment is
table1
field1 varchar(64)
other fields.
table 2.
field1 varchar(64)
other fields
I need a delete trigger on the table 1, so that if I delete a row from table 1 , the corresponding rows from table 2 should also be deleted.
This is the code I have tried.
DROP FUNCTION ApplicationsDeleteFn();
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS '
BEGIN
delete from ports where appName=OLD.appName;
RETURN OLD;
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS '
BEGIN
delete from ports where appName=OLD.appName;
RETURN OLD;
END;
'
LANGUAGE 'plpgsql';
'
LANGUAGE 'plpgsql';
Please help me with this, as my work is time bound.
Thanks
sundar
This code looks fine to me, other than missing the actual trigger statement is missing. Assuming table 1 is named apps: DROP TRIGGER OnApplicationsDelete ON apps; DROP FUNCTION ApplicationsDeleteFn(); CREATE FUNCTION ApplicationsDeleteFn() RETURNS OPAQUE AS ' BEGIN delete from ports where appName=OLD.appName; RETURN OLD; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER OnApplicationsDelete BEFORE DELETE ON apps FOR EACH ROW EXECUTE PROCEDURE ApplicationsDeleteFn(); But the question is, would it be better to use a foreign key? If you put a foreign key on ports.appName that REFERENCES apps.appName, you could define it as an ON DELETE CASCADE relationship. Meaning that deleting the value from the apps table would cascade that delete to the ports table. Hope this helps, Greg
Sundararajan wrote:
> I am developing a db application in postgresql and i need to write a delete
> trigger on one of the tables.
>
> the environment is
>
> table1
>
> field1 varchar(64)
> other fields.
>
> table 2.
>
> field1 varchar(64)
> other fields
>
> I need a delete trigger on the table 1, so that if I delete a row from table
> 1 , the corresponding rows from table 2 should also be deleted.
>
> This is the code I have tried.
>
> DROP FUNCTION ApplicationsDeleteFn();
> CREATE FUNCTION ApplicationsDeleteFn()
> RETURNS OPAQUE
> AS '
> BEGIN
> delete from ports where appName=OLD.appName;
> RETURN OLD;
>
> END;
> '
> LANGUAGE 'plpgsql';
>
> Please help me with this, as my work is time bound.
I don't see anything wrong with the trigger you have (except
that you use appName instead of field1).
But shouldn't that be a foreign key reference with ON DELETE
CASCADE and ON UPDATE CASCADE in the first place? This would
automatically delete the referencing rows, so no need for a
custom trigger. In addition, it'll ensure that you can't get
entries into ports for non-existent applications at all.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Hi,
I'm pretty new to PostgreSQL so please bear with me if this is a newbie
question that has been answered before on this list.
I have created several tables containing a SERIAL column-type as primary
key.
What I would like to do is get the last value of these columns after Ive
inserted a new row.
I know I can do this either using nextval('sequence_name') or
curval('sequence_name').
My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?
The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.
thanks in advance
Wieger
--
Wieger Uffink
tel: +31 20 428 6868
fax: +31 20 470 6905
web: http://www.usmedia.nl
> My question: > is there anyway of retreiving the sequence_name corresponding to the > respective column, > knowing just the tablename and columnname? > > The reason I need to do this, is because the application I write > dynamicly creates new tables, and I have no way of knowing the sequence > name before hand. > If it is very difficult finding that sequence name (I'm not sure) You may want to create the sequence yourself instead of using SERIAL. Then you would know the name.
Hi,
> > My question:
> > is there anyway of retreiving the sequence_name corresponding to the
> > respective column,
> > knowing just the tablename and columnname?
> >
> > The reason I need to do this, is because the application I write
> > dynamicly creates new tables, and I have no way of knowing the sequence
> > name before hand.
> >
>
>If it is very difficult finding that sequence name (I'm not sure)
>You may want to create the sequence yourself instead of using SERIAL.
>Then you would know the name.
Not hard at all.
csmith=# create table test (testid serial, name text);
NOTICE: CREATE TABLE will create implicit sequence 'test_testid_seq' for
SERIAL column 'test.testid'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testid_key'
for table 'test'
CREATE
Sequence name becomes <tablename>_<columnname>_seq
so if we use the serial datatype to create a column called "xyz" in the
table "abc" the sequence name is :
abc_xyz_seq
Make sense?
-----------------
Chris Smith
http://www.squiz.net/
On Wed, Aug 15, 2001 at 12:14:02PM +1000, Chris wrote: > >If it is very difficult finding that sequence name (I'm not sure) > >You may want to create the sequence yourself instead of using SERIAL. > >Then you would know the name. > > Not hard at all. > > csmith=# create table test (testid serial, name text); > NOTICE: CREATE TABLE will create implicit sequence 'test_testid_seq' for > SERIAL column 'test.testid' > NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testid_key' > for table 'test' > CREATE > > Sequence name becomes <tablename>_<columnname>_seq Actually, it's a bit more complicated than that. Since the maximum identifier length is 32 or so, if the above identifier comes out too long, various rules are applied to make it shorter. If you want a guarenteed name, I suggest not using serial and building the sequence yourself. Then you can use any naming convention you like. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
Hi, First of all thanks for the reactions, but I think I have probably not made my problem clear enough. I know the sequence name will be some combination, usually as 'tablename_columnname_seq', when implicitly created during table creation. But when I create my table 'maincompetencies' with serial column 'maincompetencid' the sequence is named 'maincompetenc_maincompetenc_seq'. Clearly not the same as the above mentioned naming formula. I have a hunch that table and column names are cut off after the first 13 characters, but im not sure if this is true, or will maybe change in future versions of postgre. What I would really like to do is just query Postgre, give the tablename and columnname in question, and retreive the exact sequencename. I know Postgre 'knows' this, it has that info stored somewhere, it just wont tell me :) thanks, Wieger Chris wrote: > > Hi, > > > > My question: > > > is there anyway of retreiving the sequence_name corresponding to the > > > respective column, > > > knowing just the tablename and columnname? > > > > > > The reason I need to do this, is because the application I write > > > dynamicly creates new tables, and I have no way of knowing the sequence > > > name before hand. > > > > > > >If it is very difficult finding that sequence name (I'm not sure) > >You may want to create the sequence yourself instead of using SERIAL. > >Then you would know the name. > > Not hard at all. > > csmith=# create table test (testid serial, name text); > NOTICE: CREATE TABLE will create implicit sequence 'test_testid_seq' for > SERIAL column 'test.testid' > NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testid_key' > for table 'test' > CREATE > > Sequence name becomes <tablename>_<columnname>_seq > > so if we use the serial datatype to create a column called "xyz" in the > table "abc" the sequence name is : > > abc_xyz_seq > > Make sense? > > ----------------- > Chris Smith > http://www.squiz.net/ > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Wieger Uffink tel: +31 20 428 6868 fax: +31 20 470 6905 web: http://www.usmedia.nl
On Wed, Aug 15, 2001 at 10:49:38AM +0200, Wieger Uffink wrote: > Hi, > > First of all thanks for the reactions, but I think I have probably not > made my problem clear enough. > > I know the sequence name will be some combination, usually as > 'tablename_columnname_seq', > when implicitly created during table creation. > But when I create my table 'maincompetencies' with serial column > 'maincompetencid' > the sequence is named 'maincompetenc_maincompetenc_seq'. Clearly not the > same as the above mentioned naming formula. Yes, the max identifier length is 32. > I have a hunch that table and column names are cut off after the first > 13 characters, but im not sure if this is true, or will maybe change in > future versions of postgre. It's a tricky algorithm. > What I would really like to do is just query Postgre, give the tablename > and columnname in question, and retreive the exact sequencename. I know > Postgre 'knows' this, it has that info stored somewhere, it just wont > tell me :) Well, it would be stored in the default value field of the column. You could try to parse it out of there. I suggest however not using the serial keyword, but instead building it yourself. Then you could call you sequence maincompetencies_pseq or whatever you like. Parsing it out of the system tables might not work in future versions either. The only guarenteed way is to name them yourself. HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
On Tue, 14 Aug 2001, Wieger Uffink wrote: > My question: > is there anyway of retreiving the sequence_name corresponding to the > respective column, > knowing just the tablename and columnname? > > The reason I need to do this, is because the application I write > dynamicly creates new tables, and I have no way of knowing the sequence > name before hand. > Try "tablename_fieldname_seq". That is the default sequence name for a "serial" field. Cheers. Gordan
Hi, Im accessesing a postgreSQL db through JDBC, and I would like take make use of the method, getErrorCode in the SQLException class as in java.sql. So far I have been able to retreive exception messages but not the error codes in case of an SQL exception. I would like to know if postgreSQL propegates any errorcodes at all, since I found some post earlier on this list saying it was not implemented, but that post didnt refer to any specific version. If it is could someone also point out to me where these codes are defined? I could ofcourse base my excepion handling on the messages but I'd rather use codes. thanks in advance, Wieger -- Wieger Uffink tel: +31 20 428 6868 fax: +31 20 470 6905 web: http://www.usmedia.nl
Wieger Uffink writes: > I would like to know if postgreSQL propegates any errorcodes at all, No. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Wieger, The server does not have a concept of error codes currently (it is on the TODO list). Therefore the JDBC driver has no error code to report since it doesn't get one from the backend. When the server supports error codes the JDBC driver will as well. thanks, --Barry Wieger Uffink wrote: > Hi, > > Im accessesing a postgreSQL db through JDBC, and I would like take make > use of the method, getErrorCode in the SQLException class as in > java.sql. > > So far I have been able to retreive exception messages but not the error > codes in case of an SQL exception. > > I would like to know if postgreSQL propegates any errorcodes at all, > since I found some post earlier on this list saying it was not > implemented, but that post didnt refer to any specific version. If it is > could someone also point out to me where these codes are defined? > > I could ofcourse base my excepion handling on the messages but I'd > rather use codes. > > thanks in advance, > Wieger >
hehe thank you for your elaborate though very clear reply :) thanks really Wieger Peter Eisentraut wrote: > > Wieger Uffink writes: > > > I would like to know if postgreSQL propegates any errorcodes at all, > > No. > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter -- Wieger Uffink tel: +31 20 428 6868 fax: +31 20 470 6905 web: http://www.usmedia.nl