Re: 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)