Skip navigation links

MySQL Forums :: Triggers :: "There is no NEW row in on DELETE trigger" error when creating Foreign Key


Advanced Search

"There is no NEW row in on DELETE trigger" error when creating Foreign Key
Posted by: Su Bati ()
Date: March 12, 2009 11:50PM

Hi Friends,

When I try to set up a Foreign Key constraint using PHP's MDB2->Manager Module, I get following error.

MDB2 Error: not found,_doQuery: [Error message: Could not execute statement] [Last executed query: CREATE TRIGGER USERTYPEID_FK_pk_delete_trg BEFORE DELETE ON userTypes FOR EACH ROW BEGIN SET FOREIGN_KEY_CHECKS = 0; IF (SELECT users.userTypeId AS userTypeId FROM users WHERE userTypeId = OLD.userTypeId) IS NOT NULL AND (NEW.userTypeId <> OLD.userTypeId) THEN CALL delete_ON_TABLE_users_VIOLATES_FOREIGN_KEY_CONSTRAINT(); END IF; SET FOREIGN_KEY_CHECKS = 1; END;] [Native code: 1363] [Native message: There is no NEW row in on DELETE trigger]

I have pasted relevent code below

// Now lets define the tables
$usersDefn = array (
'userId' => array (
'type' => 'text',
'length' => 30,
'notnull' => 1
),
'userKey' => array (
'type' => 'text',
'length' => 30
),
'userTypeId' => array (
'type' => 'integer',
'length' => 1,
'unsigned' => 'true',
'notnull' => 1
),
'email' => array (
'type' => 'text',
'length' => 50
),
);

$userTypesDefn = array (
'userTypeId' => array (
'type' => 'integer',
'length' => 1,
'unsigned' => 'true',
),
'userType' => array (
'type' => 'text',
'length' => 30,
),
);
// Now lets create the table all the tables
$value = 'users';
$res = $userDb->createTable($value,${$value.'Defn'});
if (PEAR::isError($res)) {
die($res->getMessage().','.$res->getDebugInfo());
}
//Now Lets add PRIMARY KEY constraints
$userTypesPKCnstrn = array (
'primary' => true,
'fields' => array (
'userTypeId' => array(),
),
);

$res = $userDb->createConstraint('userTypes','PRIMARY',
$userTypesPKCnstrn);
if (PEAR::isError($res)) {
die($res->getMessage().','.$res->getDebugInfo());
}
$usersPKCnstrn = array (
'primary' => true,
'fields' => array (
'userId' => array(),
),
);

$res = $userDb->createConstraint('users','PRIMARY',$usersPKCnstrn);
if (PEAR::isError($res)) {
die($res->getMessage().','.$res->getDebugInfo());
}

//Now Add the FOREIGN KEY constraints

$usersFK1Cnstrn = array (
'foreign' => true,
'fields' => array (
'userTypeId' => array(),
),
'references' => array (
'table' => 'userTypes',
'fields' => array (
'userTypeId' => array (),
),
),
); =======>>>>>> Code failed at this point

Am I missing something? Please help me in this matter.

Thanks
Su Ba

Options: ReplyQuote


Subject Views Written By Posted
"There is no NEW row in on DELETE trigger" error when creating Foreign Key 5693 Su Bati 03/12/2009 11:50PM
Re: "There is no NEW row in on DELETE trigger" error when creating Foreign Key 2846 Rod Sherer 03/13/2009 07:39AM
Re: "There is no NEW row in on DELETE trigger" error when creating Foreign Key 2005 Su Bati 03/13/2009 09:17PM


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.