人生第一个触发器成功+第一存储过程+事务了。-优快云博客

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

向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"
?>