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)
