Re: regarding triggers - Mailing list pgsql-general

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)