请问为何这个触发器不能正常工作，谢谢！

[hsn1982]

EDIT `haoyouju`.`t_usergroup`;

delimiter $$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `haoyouju`.`Trigger_3`
BEFORE INSERT ON `haoyouju`.`t_usergroup`
FOR EACH ROW
begin
SET new.CreateTime = NOW();
Set new.UpdateTime = now();
end
$$

CREATE TABLE `t_usergroup` (
`GroupID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(30) NOT NULL COMMENT '不可重复',
`FunciontIDs` varchar(1000) NOT NULL COMMENT '当前用户组的权限列表，自定义功能权限，由数字代表某一种权限。\n 格式为1,2,3,4。\n 例如\n 1: 用户资料查看\n 2: 用户资料修改\n 3: 用户资料删除\n 更多权限定义见参考文档。\n ',
`CreateTime` datetime NOT NULL,
`CreateUserID` int(10) unsigned NOT NULL,
`UpdateTime` datetime NOT NULL,
`UpdateUserID` int(10) unsigned NOT NULL,
PRIMARY KEY (`GroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$


执行插入语句时提示：

ERROR 1364: Field 'CreateTime' doesn't have a default value

SQL Statement:

INSERT INTO `haoyouju`.`t_usergroup` (`Name`, `FunciontIDs`, `CreateUserID`, `UpdateUserID`) VALUES ('客服', '1,2,3', 1, 1)

[ACMAIN_CHM]

由于MYSQL会先判断 插入记录是否符合条中的 NOT NULL约束，然后再执行触发器。所以会报这个错误。

1） 升级你的MYSQL到最新版本，这个这个ERROR会变为 WARNING。 但不影响你的结果。

mysql> INSERT INTO `t_usergroup` (`Name`, `FunciontIDs`, `CreateUserID`, `UpdateUserID`) VALUES ('客服', '1,2,3', 1, 1);
Query OK, 1 row affected, 2 warnings (0.14 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1364 | Field 'CreateTime' doesn't have a default value |
| Warning | 1364 | Field 'UpdateTime' doesn't have a default value |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t_usergroup;
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
| GroupID | Name | FunciontIDs | CreateTime          | CreateUserID | UpdateTime          | UpdateUserID |
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
|       1 | 客服 | 1,2,3       | 2011-06-15 18:46:14 |            1 | 2011-06-15 18:46:14 |            1 |
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
1 row in set (0.05 sec)

mysql>

2）修改你语句，对CreateTime，UpdateTime在INSERT语句中赋值。

mysql> INSERT INTO `t_usergroup` (`Name`, `FunciontIDs`, `CreateUserID`, `UpdateUserID`,CreateTime,UpdateTime) VALUES ('客服', '1,2,3', 1, 1,0,0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_usergroup;
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
| GroupID | Name | FunciontIDs | CreateTime          | CreateUserID | UpdateTime          | UpdateUserID |
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
|       3 | 客服 | 1,2,3       | 2011-06-15 18:50:27 |            1 | 2011-06-15 18:50:27 |            1 |
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
1 row in set (0.00 sec)

mysql>

3）修改你的CREATE TABLE取消 NOT NULL限制。

mysql> CREATE TABLE `t_usergroup` (
    ->  `GroupID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  `Name` varchar(30) NOT NULL ,
    ->  `FunciontIDs` varchar(1000) NOT NULL ,
    ->  `CreateTime` datetime ,
    ->  `CreateUserID` int(10) unsigned NOT NULL,
    ->  `UpdateTime` datetime ,
    ->  `UpdateUserID` int(10) unsigned NOT NULL,
    ->  PRIMARY KEY (`GroupID`)
    -> ) ;
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter $$
mysql>
mysql> CREATE
    -> TRIGGER `Trigger_3`
    -> BEFORE INSERT ON `t_usergroup`
    -> FOR EACH ROW
    -> begin
    ->    SET new.CreateTime = NOW();
    ->    Set new.UpdateTime = now();
    -> end
    -> $$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> INSERT INTO `t_usergroup` (`Name`, `FunciontIDs`, `CreateUserID`, `UpdateUserID`) VALUES ('客服', '1,2,3', 1, 1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t_usergroup;
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
| GroupID | Name | FunciontIDs | CreateTime          | CreateUserID | UpdateTime          | UpdateUserID |
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
|       1 | 客服 | 1,2,3       | 2011-06-15 18:52:12 |            1 | 2011-06-15 18:52:12 |            1 |
+---------+------+-------------+---------------------+--------------+---------------------+--------------+
1 row in set (0.00 sec)

mysql>

[rucypli]

不用触发器吧
改成默认时间戳就可以

`CreateTime` timestamp NOT NULL default DEFAULT CURRENT_TIMESTAMP

[WWWWA]

1、取消NIT NULL即可，NOT NULL的判断在TRIGGER之前；
2、任意插入非NULL值，TRIGGER会起作用。