A trigger is a good place to check the value, and change it, bit I
don't believe you can actually generate a MySQL error within the
trigger that will prevent the data from being inserted. Currently
(unless there's been an update to triggers that's not yet in the
docs), you can only change the value before it gets inserted.
If you are looking to enforce the values going into your JobType
column, you might be better off creating a JobType table, with a
foreign key restraint between the tblJob.JobType and JobType.Name,
and make sure that the only entries in the JobType.Name column are
those you want to appear in the tblJob.JobType column.
On Jun 25, 2005, at 10:28 AM, Chris Andrew wrote:
Dear List,
My system is RedHat EL3 and MySQL 5.0.7-beta.
I wanted to implement a check constraint (below), but after some
testing
and googling, it seems I can't do this with MySQL. I've read
suggestions
that check(s) should be done using triggers. Is a trigger a preferred
method of achieving the following:
CREATE TABLE tblJob (
JobId SMALLINT UNSIGNED NOT NULL,
CustomerId SMALLINT UNSIGNED NOT NULL,
JobType VARCHAR(20) NOT NULL DEFAULT 'DesignInstall',
Description VARCHAR(100) NOT NULL,
QuotationDate DATE NOT NULL,
OrderDate DATE,
CHECK (JobType IN ('DesignOnly', 'DesignInstall', 'InstallOnly')),
PRIMARY KEY (JobId, CustomerId)
) TYPE=InnoDB;
Regards,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
Mike Kruckenberg
[EMAIL PROTECTED]
"ProMySQL" Author
http://www.amazon.com/exec/obidos/ASIN/159059505X
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
