表TT
字段A,B,C
在向表TT插入一条记录时,要对字段B中信息进行检测,比喻说字段B中的信息必须满足如下格式:"数字#数字#数字#",不符合规则时,将该条记录写入到另外一张表TTP,请问这样的触发器,怎么写啊?请大家  帮帮忙啊    谢谢了   !

解决方案 »

  1.   

    不符合规则时,将该条记录写入到另外一张表TTP,--------------
    你的意思这种情况下就不往原表插入数据了?
    如果是这样的话,那不行了,因为整个操作是一个事务
      

  2.   

    mysql没有触发器回滚,不如用约束
      

  3.   

    现在 要将检测功能  改到数据库里面 进行操作     求助楼上的几位兄弟 ?
            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();
            }
      

  4.   

    格式可以考虑用正则来解决,TRIGGER中也可以
      

  5.   

    mysql> create table tt (a int,b varchar(20),c int) engine=myisam;
    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>
      

  6.   

    http://blog.csdn.net/ACMAIN_CHM/archive/2009/07/25/4380183.aspx
    MySQL 中如何在触发器里中断记录的插入或更新? 
      

  7.   

    xiexie谢谢   楼上的各位兄弟
      

  8.   


    楼上的兄弟们  按照mysql触发器的格式  创建触发器如下  报错   请帮忙看看啊
    http://topic.csdn.net/u/20091113/18/82be7853-e192-49c8-b9ff-613274702294.html
      

  9.   

    我是看你的博客上  和  你12楼回复的该的   现在在调试   出现这个上面的错误
    http://topic.csdn.net/u/20091113/18/82be7853-e192-49c8-b9ff-613274702294.html 
      

  10.   

    谢谢你们  特别是ACMAIN_CHM兄