人生第一个触发器成功+第一存储过程+事务了。

在做快捷查询时，触发器如下啊。

向cargo2中的更新数据，令cargo1中的valid字段1

create trigger test1 before insert on cargo2 for each row begin update cargo1 set valid = '1' where number = new.number; end//

create procedure Pcargo3 (in num varchar(20))
begin
select * from cargo3 where id = (select id from cargo3 where number = num
by id desc limit 1);
end;//
K, 0 rows affected (0.00 sec)

有动态处理sql语句的存储过程

create procedure pTest8(in id int,in lie varchar(20)) begin set @sql=concat('select ',lie,' from caseinfo where id = ',id); prepare stml from @sql; execute stml; end//
show procedure status --显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过 程名称，创建时间等

showcreate procedure sp_name --显示某一个存储过程的详细信息

create procedure pAttfile(in num varchar(20),in col varchar(20)) begin set @sql=concat("select ",col," from attfile where id = (select id from attfile where ",col," !='' and number = '",num,"' order by id desc limit 1)"); prepare s from @sql; execute s; end//原变量名为column试了很久未成功，改成col成功了。
function attShow($n,$c){ global $host,$user,$password,$database; $m=new mysqli($host,$user,$password,$database); $q=$m->query("call pAttfile('".$n."','".$c."')"); if($q != NULL){ $r=$q->fetch_array(MYSQLI_ASSOC); if(!$r){ return ""; }else{ switch ($r[$c]) { case 'Yes':return '<font color="red">YES</font>';break; case 'No':return '<font color="red">NO</font>';break; default: return '<a href="upload/'.$r[$c].'" target="_blank">DownLoad</a>';break; } } }else{ return "isNull"; } }
上述存储函数，为何要这般定义，不解吧，一个下午的时间均在解决此上了。原因不明啊。

不能直接global啊。不行

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

以下是在做仓储管理系统三次升级时用到的。

<?php $success=1; //disable the autocommit feature $m->autocommit(FALSE);//设置不自动提交 $sql="update basket set ORDERID =ORDERID - '9' where id='4'"; $result1=$m->query($sql); if(!$result1){ $success=0;echo 'failed1'; } //section 2 $sql2="update orders set TOPAY = TOPAY - '2' whesre id = '1'"; $result2=$m->query($sql2); if(!$result2){ $success=0;echo 'failed2'; } echo $success?"true":"false"; echo "<br>"; if($success){ $m->commit(); echo "the swap took place!Congratulatios!"; }else{ $m->rollback(); echo "there was a problem with your swap."; } $m->autocommit(TRUE); ?>
有以下注意点：

浪费了我很长时间啊MYSQL只有 INNODB和BDB类型的数据表才支持事务处理，其他的类型是不支持的!

<?php include_once("conn.php"); $id=$_GET[id]; $conn->autocommit(false); if(!$conn->query("delete from tb_sco where id='".$id."'")) { $conn->rollback(); } if(!$conn->query("delete from tb_stu where id='".$id."'")) { $conn->rollback(); } $conn->commit(); $conn->autocommit(true); echo "ok" ?>