MySQL Forums
Forum List  »  Quality Assurance

Before Insert Trigger not taking effect before insert
Posted by: Julie Medalie
Date: June 06, 2011 07:10AM

I am trying to add a trigger to a table that will change the value of a column if it is Null. The column is part of the primary key. I want the Null value to be changed to the outcome of an expression (see below for specifics) before it is inserted into the table.

I get the following error: "1364 - Field 'RDATE' doesn't have a default value". Shouldn't my trigger be taking effect before mySQL attempts to insert a default value?

Here's the trigger:
BEGIN
IF NEW.RDATE IS NULL THEN
SET NEW.RDATE = STR_TO_DATE(NEW.RDATEstring,'%m/%d/%Y');
ELSE
SET NEW.RDATEstring = CONCAT(MONTH(NEW.RDATE),"/",DAY(NEW.RDATE),"/",YEAR(NEW.RDATE));
END IF;
END

I have my global sql_mode set to "TRADITIONAL".

If anyone can think of a solution to my problem, I would greatly appreciate it. I would also be interested to understand when the before insert trigger is being run with regards to attempting to insert a Null value into a primary key column.

Thanks,
Julie

Options: ReplyQuote


Subject
Views
Written By
Posted
Before Insert Trigger not taking effect before insert
2441
June 06, 2011 07:10AM


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.