Before insert trigger fires on delete
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
Subject
Views
Written By
Posted
Before insert trigger fires on delete
4811
February 22, 2006 07:31PM
2353
February 23, 2006 04:45AM
2337
February 23, 2006 08:44AM
2431
February 23, 2006 09:47AM
2440
February 23, 2006 01:42PM
2131
March 02, 2009 07:34AM
3427
February 24, 2006 10:36AM
2575
February 24, 2006 01:27PM
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.