MySQL Forums
Forum List  »  Triggers

Before insert trigger fires on delete
Posted by: Murthy Vempaty
Date: February 22, 2006 07:31PM

I am trying to implement FK constraint on an MyIsam table using triggers. So I have created a before insert trigger like below:

DELIMITER $$

DROP TRIGGER bi_hospital_doctor$$

CREATE TRIGGER test.bi_hd BEFORE INSERT on test.hd
FOR EACH ROW
BEGIN
DECLARE l_cnt int ;
DECLARE l_name varchar(2);

select count(*) into l_cnt
from test.d
where d.id = new.d_id;

if ( l_cnt = 0 ) then
select name into l_name from `test`.`parentnotfound`;
end if;

END$$

DELIMITER ;
There are two problems with this trigger:
1. When I try to insert into hd a row either with parent row existing or not existing in table d, it raises error.
2. the trigger fires when I try to delete from table hd (child table). Why is a before insert trigger firing on delete?
We are running MySql 5.0.18 on WindowsXP professional.
Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Before insert trigger fires on delete
4811
February 22, 2006 07:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.