Re: [GENERAL] regarding triggers
Title: RE: [GENERAL] regarding triggers but if i have on delete cascade constraint, in that case if i have a trigger which is fired in case delet happens on the table y. i have a table x, and table y has a foreign key with on delete cascade constraint, now i delete a row from x, will the trigger still be called? -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Tue 1/10/2006 9:58 PM To: John McCawley Cc: surabhi.ahuja; pgsql-general@postgresql.org Subject: Re: [GENERAL] regarding triggers *** Your mail has been scanned by iiitb VirusWall. ***-*** John McCawley [EMAIL PROTECTED] writes: Foreign keys do not cascade deletions. By default, no, but there is the CASCADE option ... regards, tom lane
Re: [GENERAL] regarding triggers
surabhi.ahuja wrote:
but if i have on delete cascade constraint,
in that case if i have a trigger which is fired in case delet happens
on the table y.
i have a table x, and table y has a foreign key with on delete
cascade constraint,
now i delete a row from x, will the trigger still be called?
I just did a test, and it does. See below (note my serial_id on the log
table is incremented from earlier testing)
create table tbl_foo ( foo_id SERIAL PRIMARY KEY, stuff varchar(32) );
create table tbl_bar ( bar_id SERIAL PRIMARY KEY, foo_id integer,
barstuff varchar(32) );
create table tbl_log ( log_id SERIAL PRIMARY KEY, stuff varchar(32) );
ALTER TABLE tbl_bar ADD CONSTRAINT fk_tbl_bar_tbl_foo_foo_id FOREIGN KEY
(foo_id) REFERENCES tbl_foo(foo_id) MATCH FULL ON DELETE CASCADE;
CREATE FUNCTION sp_logdelete() RETURNS trigger AS '
DECLARE
BEGIN
INSERT INTO tbl_log (stuff) VALUES (\'Trigger was called!\');
return OLD;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER bar_delete_trigger BEFORE DELETE ON tbl_bar FOR EACH ROW
EXECUTE PROCEDURE sp_logdelete();
INSERT INTO tbl_foo (stuff) VALUES ('this is stuff');
select * FROM tbl_foo;
foo_id | stuff
+---
1 | this is stuff
(1 row)
insert into tbl_bar (foo_id, barstuff) VALUES (1, 'bar stuff');
select * FROM tbl_log;
log_id | stuff
+---
(0 rows)
delete from tbl_foo;
DELETE 1
SELECT * FROM tbl_log;
log_id |stuff
+-
5 | Trigger was called!
(1 row)
SELECT * FROM tbl_bar;
bar_id | foo_id | barstuff
++--
(0 rows)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] regarding triggers
Title: regarding triggers is there an advantage of using a trigger? when the same job can be performed by a stored procedure? one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) thanks, regards Surabhi
Re: [GENERAL] regarding triggers
Foreign keys do not cascade deletions. If table y references table x on column a, the attempt to delete records in x with dependent records in y will yield an error. So the answer to that question is no, your trigger won't get called because a) y doesn't get touched because that's not what foreign keys do and b) an error is raised anyway Regarding the usefulness of triggers...I tend to stay away from them. I like to keep my data in my database and my logic in my application. I try to relegate triggers to very simple things like timestamping records. i.e. things that I won't later wonder What in the hell is going on??? surabhi.ahuja wrote: is there an advantage of using a trigger? when the same job can be performed by a stored procedure? one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) thanks, regards Surabhi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] regarding triggers
On 1/10/06, surabhi.ahuja [EMAIL PROTECTED] wrote: is there an advantage of using a trigger? when the same job can be performed by a stored procedure? a trigger is actually a stored procedure... the advantage is that it's called automagically when an event happens... one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. only if you specified ON DELETE CASCADE at FOREIGN KEY creation now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) if the DELETE will CASCADE, yes thanks, regards Surabhi -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] regarding triggers
On 1/10/06, Jaime Casanova [EMAIL PROTECTED] wrote: On 1/10/06, surabhi.ahuja [EMAIL PROTECTED] wrote: is there an advantage of using a trigger? when the same job can be performed by a stored procedure? a trigger is actually a stored procedure... the advantage is that it's called automagically when an event happens... one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. only if you specified ON DELETE CASCADE at FOREIGN KEY creation now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) if the DELETE will CASCADE, yes thanks, regards Surabhi -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] regarding triggers
John McCawley wrote: Foreign keys do not cascade deletions. They will if you specify on delete cascade. -- Guy Rouillier ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] regarding triggers
John McCawley [EMAIL PROTECTED] writes: Foreign keys do not cascade deletions. By default, no, but there is the CASCADE option ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] regarding triggers
On 1/10/06, John McCawley [EMAIL PROTECTED] wrote: Regarding the usefulness of triggers...I tend to stay away from them. I like to keep my data in my database and my logic in my application. I try to relegate triggers to very simple things like timestamping records. i.e. things that I won't later wonder What in the hell is going on??? I always try to get all the relationships from the data into the database using whatever the database can do ie triggers, foriegn keys, check constraints etc. I find that leaving all the logic to the application is a disaster waiting to happen particularly when the application is being developed by lots of people. If you insist in having all the logic in the application then surely you could use triggers to make sure that if the application makes a cock up then the integrity of the data won't be compromised. I know you can use begin; commit; from the application but when working with other developers there are no guarantees that they will always be using them or more likely a mistake will be made by me or someone else and I want the database to handle it. -- Harry http://www.hjackson.org http://www.uklug.co.uk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
