高手帮忙看看触发程序如何写？

Question

create table a {
 id int(4) not null AUTO_INCREMENT,
 para1 float,
 para2 float,
 para3 float,
 para4 float,
 fpara1 tinyint(1),
 fpara2 tinyint(1),
 fpara3 tinyint(1),
 fpara4 tinyint(1)}
create table b{
 id int(4) not null AUTO_INCREMENT,
 foreignName varchar(45),
 up float,
 down float
}delimeter //create trigger judgeBoolean before insert on a 
  for each row begin
      if NEW.para1>(select down from b where foreignName='para1') and NEW.para1<(select up from b where foreignName='para1') then set fpara1=0;
      else set fpara1=1;
  end if; //
请问我这样写对么？

Answer 1

建议楼上自己先试一下你的这些SQL语句。先把基本的语法错误消除，然后再贴出来。否则别人贴过去也是一堆错误信息。结果反而不知道你是真的语法错误还是逻辑上的错误。

Answer 2

SQL触发器语法
　　语法
　　CREATE TRIGGER trigger_name
　　ON { table | view }
　　[ WITH ENCRYPTION ]
　　{
　　{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ DELETE ] [ UPDATE ] }
　　[ WITH APPEND ]
　　[ NOT FOR REPLICATION ]
　　AS
　　[ { IF UPDATE ( column )
　　[ { AND | OR } UPDATE ( column ) ]
　　[ ...n ]
　　| IF ( COLUMNS_UPDATED ( ) updated_bitmask )
　　column_bitmask [ ...n ]
　　} ]
　　sql_statement [ ...n ]
　　}
　　}

Answer 3

详细参考：http://baike.baidu.com/view/1189954.htm

Answer 4

create trigger judgeBoolean before insert on a 
  for each row begin 
      if NEW.para1>(select down from b where foreignName='para1') and NEW.para1 <(select up from b where foreignName='para1') then set new.fpara1=0; 
      else set new.fpara1=1; 
  end if; //

Answer 5

delimeter // 
create table a (
id int(4) not null AUTO_INCREMENT PRIMARY KEY, 
para1 float, 
para2 float, 
para3 float, 
para4 float, 
fpara1 tinyint(1), 
fpara2 tinyint(1), 
fpara3 tinyint(1), 
fpara4 tinyint(1) 
)
create table b( 
id int(4) not null AUTO_INCREMENT PRIMARY KEY, 
foreignName varchar(45), 
up float, 
down float 
)create trigger judgeBoolean before insert on a 
  FOR EACH ROW BEGIN
      if NEW.para1>(select down from b where foreignName='para1') and NEW.para1 <(select up from b where foreignName='para1') then set fpara1=0; 
      else set fpara1=1; 
  END IF; 
  END;// 
然后我开始输入数据以及创建触发器如下：
delimiter  //INSERT INTO b (foreignname,up,down)  values('para1',0.53,0.24),('para2',0.27,0.15),('para3',1.5,0.87),('para4',2.3,1.9);
create trigger judgeBoolean before insert on a 
  for each row begin 
      if NEW.para1>(select down from b where foreignName='para1') and NEW.para1 <(select up from b where foreignName='para1') then set NEW.fpara1=0; 
      else set NEW.fpara1=1; 
  end if; 
  end;
//随后我对表a进行测试：insert into a (para1,para2,para3,para4) values (5.0,0.5,0.7,2.3);结果如下：'1', '5', '0.5', '0.7', '2.3', '1', NULL, NULL, NULL
如果我把para2,para3,para4也都做标记应该怎么做？

Answer 6

你这个结果没错啊！NEW.para1>up and NEW.para1>down-->NEW.fpara1=1

Answer 7

 public static void insert(PolyCheck polyCheck){
        Connection con=UtilClass.getCon();
        PreparedStatement pstat=null;
        try{
                pstat=con.prepareStatement("insert into polycheck ( allocatePipePressureA,allocatePipePressureB,allocatePipePressureC,se_A_1,se_A_2,se_A_3,se_A_4,se_A_5,se_A_6,se_A_7," +
      "se_A_8,se_B_1,se_B_2,se_B_3,se_B_4,se_B_5,se_B_6,se_B_7,se_B_8,se_C_1,se_C_2,se_C_3,se_C_4,se_C_5,se_C_6,se_C_7,se_C_8,he301_1NH3_L,he301_1NH3_T,he301_2NH3_L,he301_2NH3_T,he301_3NH3_L," +
      "he301_3NH3_T,opu301A_P,opu301B_P,opu301C_P,opu301D_P,opu301E_F_P,tk301_NH3,tk302_NH3,tk303_NH3,tdmA,tdmB,tdmC,shsA,shsB,shsC,opu301A_L,opu301B_L,opu301C_L,opu301D_L,opu301E_F_L," +
      "workgroup,operator,readyworkgroup,turnOperator,recordtime) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                pstat.setFloat(1, polyCheck.getAllocatePipePressureA());
                pstat.setFloat(2, polyCheck.getAllocatePipePressureB());
                pstat.setFloat(3, polyCheck.getAllocatePipePressureC());
                pstat.setFloat(4, polyCheck.getSe_A_1());
                pstat.setFloat(5, polyCheck.getSe_A_2());
                pstat.setFloat(6, polyCheck.getSe_A_3());
                pstat.setFloat(7, polyCheck.getSe_A_4());
                pstat.setFloat(8, polyCheck.getSe_A_5());
                pstat.setFloat(9, polyCheck.getSe_A_6());
                pstat.setFloat(10, polyCheck.getSe_A_7());
                pstat.setFloat(11, polyCheck.getSe_A_8());
                pstat.setFloat(12, polyCheck.getSe_B_1());
                pstat.setFloat(13, polyCheck.getSe_B_2());
                pstat.setFloat(14, polyCheck.getSe_B_3());
                pstat.setFloat(15, polyCheck.getSe_B_4());
                pstat.setFloat(16, polyCheck.getSe_B_5());
                pstat.setFloat(17, polyCheck.getSe_B_6());
                pstat.setFloat(18, polyCheck.getSe_B_7());
                pstat.setFloat(19, polyCheck.getSe_B_8());
                pstat.setFloat(20, polyCheck.getSe_C_1());
                pstat.setFloat(21, polyCheck.getSe_C_2());
                pstat.setFloat(22, polyCheck.getSe_C_3());
                pstat.setFloat(23, polyCheck.getSe_C_4());
                pstat.setFloat(24, polyCheck.getSe_C_5());
                pstat.setFloat(25, polyCheck.getSe_C_6());
                pstat.setFloat(26, polyCheck.getSe_C_7());
                pstat.setFloat(27, polyCheck.getSe_C_8());
                pstat.setFloat(28, polyCheck.getHe301_1NH3_L());
                pstat.setFloat(29, polyCheck.getHe301_1NH3_T());
                pstat.setFloat(30, polyCheck.getHe301_2NH3_L());
                pstat.setFloat(31, polyCheck.getHe301_2NH3_T());
                pstat.setFloat(32, polyCheck.getHe301_3NH3_L());
                pstat.setFloat(33, polyCheck.getHe301_3NH3_T());
                pstat.setFloat(34, polyCheck.getOpu301A_P());
                pstat.setFloat(35, polyCheck.getOpu301B_P());
                pstat.setFloat(36, polyCheck.getOpu301C_P());
                pstat.setFloat(37, polyCheck.getOpu301D_P());
                pstat.setFloat(38, polyCheck.getOpu301E_F_P());
                pstat.setFloat(39, polyCheck.getTk301_NH3());
                pstat.setFloat(40, polyCheck.getTk302_NH3());
                pstat.setFloat(41, polyCheck.getTk303_NH3());
                pstat.setFloat(42, polyCheck.getTdmA());
                pstat.setFloat(43, polyCheck.getTdmB());
                pstat.setFloat(44, polyCheck.getTdmC());
                pstat.setFloat(45, polyCheck.getShsA());
                pstat.setFloat(46, polyCheck.getShsB());
                pstat.setFloat(47, polyCheck.getShsC());
                pstat.setFloat(48, polyCheck.getOpu301A_L());
                pstat.setFloat(49, polyCheck.getOpu301B_L());
                pstat.setFloat(50, polyCheck.getOpu301C_L());
                pstat.setFloat(51, polyCheck.getOpu301D_L());
                pstat.setFloat(52, polyCheck.getOpu301E_F_L());
                pstat.setString(53, UtilClass.codeChanger(polyCheck.getWorkgroup()));
                pstat.setString(54, UtilClass.codeChanger(polyCheck.getOperator()));
                pstat.setString(55, UtilClass.codeChanger(polyCheck.getReadyworkgroup()));
                pstat.setString(56, UtilClass.codeChanger(polyCheck.getTurnOperator()));
                pstat.setBoolean(57, judgeBoolean("allocatePipePressureA",polyCheck.getAllocatePipePressureA()));
                pstat.setBoolean(58, judgeBoolean("allocatePipePressureB",polyCheck.getAllocatePipePressureB()));
                pstat.setBoolean(59, judgeBoolean("allocatePipePressureC",polyCheck.getAllocatePipePressureC()));
                pstat.setBoolean(60, judgeBoolean("se_A_1",polyCheck.getSe_A_1()));
                pstat.setBoolean(61, judgeBoolean("se_A_2",polyCheck.getSe_A_2()));
                pstat.setBoolean(62, judgeBoolean("se_A_3",polyCheck.getSe_A_3()));
                pstat.setBoolean(63, judgeBoolean("se_A_4",polyCheck.getSe_A_4()));
                pstat.setBoolean(64, judgeBoolean("se_A_5",polyCheck.getSe_A_5()));
                pstat.setBoolean(65, judgeBoolean("se_A_6",polyCheck.getSe_A_6()));
                pstat.setBoolean(66, judgeBoolean("se_A_7",polyCheck.getSe_A_7()));
                pstat.setBoolean(67, judgeBoolean("se_A_8",polyCheck.getSe_A_8()));
                pstat.setBoolean(68, judgeBoolean("se_B_1",polyCheck.getSe_B_1()));
                pstat.setBoolean(69, judgeBoolean("se_B_2",polyCheck.getSe_B_2()));
                pstat.setBoolean(70, judgeBoolean("se_B_3",polyCheck.getSe_B_3()));
                pstat.setBoolean(71, judgeBoolean("se_B_4",polyCheck.getSe_B_4()));
                pstat.setBoolean(72, judgeBoolean("se_B_5",polyCheck.getSe_B_5()));
                pstat.setBoolean(73, judgeBoolean("se_B_6",polyCheck.getSe_B_6()));
                pstat.setBoolean(74, judgeBoolean("se_B_7",polyCheck.getSe_B_7()));
                pstat.setBoolean(75, judgeBoolean("se_B_8",polyCheck.getSe_B_8()));
                pstat.setBoolean(76, judgeBoolean("se_C_1",polyCheck.getSe_C_1()));
                pstat.setBoolean(77, judgeBoolean("se_C_2",polyCheck.getSe_C_2()));
                pstat.setBoolean(78, judgeBoolean("se_C_3",polyCheck.getSe_C_3()));
                pstat.setBoolean(79, judgeBoolean("se_C_4",polyCheck.getSe_C_4()));
                pstat.setBoolean(80, judgeBoolean("se_C_5",polyCheck.getSe_C_5()));
                pstat.setBoolean(81, judgeBoolean("se_C_6",polyCheck.getSe_C_6()));
                pstat.setBoolean(82, judgeBoolean("se_C_7",polyCheck.getSe_C_7()));
                pstat.setBoolean(83, judgeBoolean("se_C_8",polyCheck.getSe_C_8()));
                pstat.setBoolean(84, judgeBoolean("he301_1NH3_L",polyCheck.getHe301_1NH3_L()));
                pstat.setBoolean(85, judgeBoolean("he301_1NH3_T",polyCheck.getHe301_1NH3_T()));
                pstat.setBoolean(86, judgeBoolean("he301_2NH3_L",polyCheck.getHe301_2NH3_L()));
                pstat.setBoolean(87, judgeBoolean("he301_2NH3_T",polyCheck.getHe301_2NH3_T()));
                pstat.setBoolean(88, judgeBoolean("he301_3NH3_L",polyCheck.getHe301_3NH3_L()));
                pstat.setBoolean(89, judgeBoolean("he301_3NH3_T",polyCheck.getHe301_3NH3_T()));
                pstat.setBoolean(90, judgeBoolean("opu301A_P",polyCheck.getOpu301A_P()));
                pstat.setBoolean(58, judgeBoolean("opu301B_P",polyCheck.getOpu301B_P()));
                pstat.setBoolean(58, judgeBoolean("opu301C_P",polyCheck.getOpu301C_P()));
                pstat.setBoolean(58, judgeBoolean("opu301D_P",polyCheck.getOpu301D_P()));
                pstat.setBoolean(58, judgeBoolean("opu301E_F_P",polyCheck.getOpu301E_F_P()));
                pstat.setBoolean(58, judgeBoolean("tk301_NH3",polyCheck.getTk301_NH3()));
                pstat.setBoolean(58, judgeBoolean("tk302_NH3",polyCheck.getTk302_NH3()));
                pstat.setBoolean(58, judgeBoolean("tk303_NH3",polyCheck.getTk303_NH3()));
                pstat.setBoolean(58, judgeBoolean("tdmA",polyCheck.getTdmA()));
                pstat.setBoolean(58, judgeBoolean("tdmB",polyCheck.getTdmB()));
                pstat.setBoolean(58, judgeBoolean("tdmC",polyCheck.getTdmC()));
                pstat.setBoolean(58, judgeBoolean("shsA",polyCheck.getShsA()));
                pstat.setBoolean(58, judgeBoolean("shsB",polyCheck.getShsB()));
                pstat.setBoolean(58, judgeBoolean("shsC",polyCheck.getShsC()));
                pstat.setBoolean(58, judgeBoolean("opu301A_L",polyCheck.getOpu301A_L()));
                pstat.setBoolean(58, judgeBoolean("opu301B_L",polyCheck.getOpu301B_L()));
                pstat.setBoolean(58, judgeBoolean("opu301C_L",polyCheck.getOpu301C_L()));
                pstat.setBoolean(58, judgeBoolean("opu301D_L",polyCheck.getOpu301D_L()));
                pstat.setBoolean(58, judgeBoolean("opu301E_F_L",polyCheck.getOpu301E_F_L()));
                // System.out.println(polyCheck.getAllocatePipePressureA());
                //System.out.println(UtilClass.codeChanger(polyCheck.getWorkgroup()));
                pstat.executeUpdate();        }catch(Exception e){
            e.printStackTrace();
        }
        finally{
            UtilClass.closePstat(pstat);
            UtilClass.closeCon(con);
        }    }
如果不使用数据库语言进行循环操作，结果写出来的就是这个样子，而且，还有另外十张同样的表，数据都不必这少。这个，仅仅是个函数而已，我敲了大概半个小时才搞定。

Answer 8

mysql> select * from b;
+----+-------------+------+------+
| id | foreignName | up   | down |
+----+-------------+------+------+
|  1 | para1       | 0.53 | 0.24 |
|  2 | para2       | 0.27 | 0.15 |
|  3 | para3       |  1.5 | 0.87 |
|  4 | para4       |  2.3 |  1.9 |
+----+-------------+------+------+
4 rows in set (0.00 sec)mysql> delimiter  //
mysql> create trigger judgeBoolean before insert on a
    -> for each row begin
    ->  set NEW.fpara1=(select count(*) from b where foreignName='para1' and new.para1 between down and up);
    ->  set NEW.fpara2=(select count(*) from b where foreignName='para2' and new.para2 between down and up);
    ->  set NEW.fpara3=(select count(*) from b where foreignName='para3' and new.para3 between down and up);
    ->  set NEW.fpara4=(select count(*) from b where foreignName='para4' and new.para4 between down and up);
    -> end;
    -> //
Query OK, 0 rows affected (0.11 sec)mysql> delimiter ;
mysql> insert into a (para1,para2,para3,para4) values (5.0,0.5,0.7,2.3);
Query OK, 1 row affected (0.06 sec)mysql> select * from a;
+----+-------+-------+-------+-------+--------+--------+--------+--------+
| id | para1 | para2 | para3 | para4 | fpara1 | fpara2 | fpara3 | fpara4 |
+----+-------+-------+-------+-------+--------+--------+--------+--------+
|  3 |     5 |   0.5 |   0.7 |   2.3 |      0 |      0 |      0 |      1 |
+----+-------+-------+-------+-------+--------+--------+--------+--------+
1 row in set (0.00 sec)mysql>

Answer 9

to ACMAIN_CHM:首先，谢谢你！假设我的a表有52个float型的字段，能不能用sql语句得到一个a表所有字段的一个集合，然后使用for循环来动态执行NEW.fpara4=(select count(*) from b where foreignName='para4' and new.para4 between down and up);这句。其中，fpara4和para4都是变量。

Answer 10

不能。(select count(*) from b where foreignName='para4' and new.para4 between down and up); 倒是可以 foreignName=v_xxx但前面new.xxx 不行。你复制一下，是不难事儿啊。不过才52个，就算是100个在ＥＸＣＥＬ中也一分钟就做完了。

Answer 11

谢谢你！呵呵，我知道这些就行了，省去我看mysql的时间，而且给我争取了时间，我相信你是高手。至于1分钟那是不可能的，为了提问举例方面，我都把参数写成para1,para2的形式，便于观看和理解。实际上，我的52参数每个都不一样，不过你的方法至少比我的方法要省事的多，而且高效。

Answer 12

desc table 得到字段名。10s贴入EXCEL A列 10s
B1列写入公式  ="NEW."&A1&"=(select count(*) from b where foreignName='"&A1&"' and new.para4 between down and up);"30s向下填充。10s然后复制回你的触发器。

Answer 13

我用的mysql workbench好像不能单独把A列选择出来，ACMAIN_CHM用的是什么软件？

Answer 14

命令行工具是最好的工具.mysql> desc b;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| aid     | int(11)     | YES  |     | NULL    |       |
| content | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql>aid
content