Thread: 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
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 >
On 1/10/06, surabhi.ahuja <surabhi.ahuja@iiitb.ac.in> 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 ;)
On 1/10/06, Jaime Casanova <systemguards@gmail.com> wrote: > On 1/10/06, surabhi.ahuja <surabhi.ahuja@iiitb.ac.in> 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 ;)
John McCawley wrote: > Foreign keys do not cascade deletions. They will if you specify "on delete cascade". -- Guy Rouillier
John McCawley <nospam@hardgeus.com> writes:
> Foreign keys do not cascade deletions.
By default, no, but there is the CASCADE option ...
regards, tom lane
On 1/10/06, John McCawley <nospam@hardgeus.com> 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
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:tgl@sss.pgh.pa.us]
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 <nospam@hardgeus.com> writes:
> Foreign keys do not cascade deletions.
By default, no, but there is the CASCADE option ...
regards, tom lane
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)