表TT
字段A,B,C
在向表TT插入一条记录时,要对字段B中信息进行检测,比喻说字段B中的信息必须满足如下格式:"数字#数字#数字#",不符合规则时,将该条记录写入到另外一张表TTP,请问这样的触发器,怎么写啊?请大家 帮帮忙啊 谢谢了 !
字段A,B,C
在向表TT插入一条记录时,要对字段B中信息进行检测,比喻说字段B中的信息必须满足如下格式:"数字#数字#数字#",不符合规则时,将该条记录写入到另外一张表TTP,请问这样的触发器,怎么写啊?请大家 帮帮忙啊 谢谢了 !
你的意思这种情况下就不往原表插入数据了?
如果是这样的话,那不行了,因为整个操作是一个事务
private void AnaAction_Execute(object sender, SimpleActionExecuteEventArgs e)
{
//没有数据时 都为null
string sqlcount= "SELECT COUNT(smsindex) FROM un_analysis";
string count = getCount(sqlcount); if (count != null||count!="")
{
string sql = "SELECT * FROM un_analysis";
DataTable dt = getTable(sql);
foreach (DataRow dr in dt.Rows)
{
int smsindex = Convert.ToInt32(dr["smsindex"].ToString());
string phone = dr["SourceNumber"].ToString().Trim();//.Substring(2, 11);//手机号;
string txt = ToDBC(dr["Content"].ToString());
string sTime = dr["SentTime"].ToString(); string sql3 = "SELECT eid, Titleof FROM employeelist WHERE mobilephone='" + phone.Substring(2, 11) + "'";
string eidName = getName("eid", "Titleof", sql3);//员工编号*员工姓名
string[] pp = new string[2];
pp = eidName.Split('*'); if (eidName == null || eidName == "")//非本公司员工
{
string content = "您非本公司员工,不能完成此操作 " + txt;//
string sqlerr = "INSERT INTO t_sendtask(destnumber,content) VALUES('" + phone + "','" + content + "')";//INSERT INTO t_sendtask(destnumber,content) VALUES('13758171477','sdfsdfsd')
inserValue(sqlerr);
string sqlerr1 = "UPDATE t_recrecord SET msgtype=6 WHERE smsindex=" + smsindex;
inserValue(sqlerr1);//标记有误短信
}
else
{ string[] conTxt = txt.Trim().Split('#'); if ((conTxt.Length < 4 || conTxt.Length > 12) || conTxt.Length % 2 != 0)//格式不对
{
//string[] temp3 = "1#1#1#".Split('#');
//string[] temp4 = "1#1#1#1#1#1#1#1#1#1#1#".Split('#');
string content = "格式不对 " + txt;//
string sqlerr = "INSERT INTO t_sendtask(destnumber,content) VALUES('" + phone + "','" + content + "')";//INSERT INTO t_sendtask(destnumber,content) VALUES('13758000477','sdfsdfsd')
inserValue(sqlerr);
string sqlerr1 = "UPDATE t_recrecord SET msgtype=2 WHERE smsindex=" + smsindex;
inserValue(sqlerr1);//标记有误短信
}
else
{
string cid = conTxt[0];//客户编号
int cid1 = Convert.ToInt32(cid);
string sql1 = "SELECT accountname FROM vtiger_account WHERE accountid=" + cid1;
string cName = getName("accountname", sql1); //客户名
if (cName.Trim() == null || cName == "")
{
string content = "客户编码不对 " + txt;//是否需要加上 短信接收时间
string sqlerr = "INSERT INTO t_sendtask(destnumber,content) VALUES('" + phone + "','" + content + "')";//INSERT INTO t_sendtask(destnumber,content) VALUES('13758171477','sdfsdfsd')
inserValue(sqlerr);
string sqlerr1 = "UPDATE t_recrecord SET msgtype=3 WHERE smsindex=" + smsindex;
inserValue(sqlerr1);//标记有误短信
}
else
{
bool productflag = true;//标记所有产品编码 是否正确 for (int i = 1; i < (conTxt.Length - 2); )//单条产品记录
{ string pid = conTxt[i];//商品编号
string pcount = conTxt[i + 1];//数量 string reg = @"^([1-9]|[1-9][0-9]|[1-9][0-9][0-9])$";
Regex re = new Regex(reg);
Match m = re.Match(pcount);
if (m.Success)
{
//订单表中销售数量是字符型? string sql2 = "SELECT productname FROM vtiger_products WHERE productcode='" + pid + "'"; string pName = getName("productname", sql2);//SELECT productname FROM vtiger_products WHERE productcode='10001'//产品名 if (pName.Trim() == null || pName == "")//编码不对
{
string content = "产品编码不对 " + txt;//是否需要加上 短信接收时间
string sqlerr = "INSERT INTO t_sendtask(destnumber,content) VALUES('" + phone + "','" + content + "')";//INSERT INTO t_sendtask(destnumber,content) VALUES('13758171477','sdfsdfsd')
inserValue(sqlerr);
string sqlerr1 = "UPDATE t_recrecord SET msgtype=4 WHERE smsindex=" + smsindex;
inserValue(sqlerr1);//标记有误短信
productflag = false;
goto NextProduct;//只要该条产品记录信息有误 就跳出循环 }
else
{
//检测完所有的商品编码后 才能解析
}
}
else
{
string content = "数量编码不对 " + txt;//是否需要加上 短信接收时间
string sqlerr = "INSERT INTO t_sendtask(destnumber,content) VALUES('" + phone + "','" + content + "')";//INSERT INTO t_sendtask(destnumber,content) VALUES('13758171477','sdfsdfsd')
inserValue(sqlerr);
string sqlerr1 = "UPDATE t_recrecord SET msgtype=5 WHERE smsindex=" + smsindex;
inserValue(sqlerr1);//标记有误短信 productflag = false;
goto NextProduct;//只要该条产品记录信息有误 就跳出循环
} i = i + 2;
}//单条产品记录 NextProduct:
if (productflag)//编码没有错误 才解析
{
for (int i = 1; i < (conTxt.Length - 2); )//单条产品记录
{
string pid = conTxt[i];//商品编号
string pcount = conTxt[i + 1];//数量 string sql2 = "SELECT productname FROM vtiger_products WHERE productcode='" + pid + "'";
string pName = getName("productname", sql2); #region phone=null
//if (eidName != null)
//{
// //
//}
#endregion //INSERT INTO vtiger_salesorder(adjustment,accountid,total,customerno,subtotal,purchaseorder,SUBJECT,duedate,sostatus) VALUES('','','','','','','','','')
string sql4 = "INSERT INTO vtiger_salesorder(adjustment,accountid,total,customerno,subtotal,purchaseorder,SUBJECT,duedate,sostatus)"
+ " VALUES('" + pp[0] + "','" + pp[1] + "','" + cid + "','" + cName + "','" + pid + "','" + pName + "','" + pcount + "','" + sTime + "','" + smsindex + "')"; inserValue(sql4);//将解析内容写入订单表 string sql5 = "UPDATE t_recrecord SET msgtype=1 WHERE smsindex=" + smsindex;//UPDATE t_recrecord SET msgtype=1 WHERE smsindex=1
inserValue(sql5);//更新收件箱
i = i + 2;
}
} } }
}
} //下一条短信
} View.Refresh();
}
Query OK, 0 rows affected (0.06 sec)mysql> create table ttp (a int,b varchar(20),c int) engine=myisam;
Query OK, 0 rows affected (0.03 sec)mysql> create table t_Rookie_CEO(x int not null) engine=myisam;
Query OK, 0 rows affected (0.03 sec)mysql>
mysql> delimiter |
mysql>
mysql> CREATE TRIGGER tr_tt_bi BEFORE INSERT ON tt
-> FOR EACH ROW BEGIN
-> IF new.b not regexp '^[0-9]*#[0-9]*#[0-9]*#$' THEN
-> insert into ttp value(new.a,new.b,new.c);
-> insert into t_Rookie_CEO value(null);
-> END IF;
-> END;
-> |
Query OK, 0 rows affected (0.09 sec)mysql> delimiter ;
mysql>
mysql> insert into tt values(1,'1#2#3#',100);
Query OK, 1 row affected (0.01 sec)mysql> insert into tt values(2,'A1#2#3#',200);
ERROR 1048 (23000): Column 'x' cannot be null
mysql> select * from tt;
+------+--------+------+
| a | b | c |
+------+--------+------+
| 1 | 1#2#3# | 100 |
+------+--------+------+
1 row in set (0.00 sec)mysql> select * from ttp;
+------+---------+------+
| a | b | c |
+------+---------+------+
| 2 | A1#2#3# | 200 |
+------+---------+------+
1 row in set (0.00 sec)mysql>
MySQL 中如何在触发器里中断记录的插入或更新?
楼上的兄弟们 按照mysql触发器的格式 创建触发器如下 报错 请帮忙看看啊
http://topic.csdn.net/u/20091113/18/82be7853-e192-49c8-b9ff-613274702294.html
http://topic.csdn.net/u/20091113/18/82be7853-e192-49c8-b9ff-613274702294.html