mysql触发器中要回滚的话_在MySQL触发器中使用预准备语句的替代方法

我正在尝试使用以下代码创建一个MySQL插入之前触发器,如果??我能找到一种方法来执行触发器生成的准备好的语句,它将执行我想要的事情.

是否有其他方法可以从触发器内部执行准备好的语句？谢谢

BEGIN

SET @CrntRcrd = (SELECT AUTO_INCREMENT FROM information_schema.TABLES

WHERE TABLE_SCHEMA=DATABASE()

AND TABLE_NAME='core_Test');

SET @PrevRcrd = @CrntRcrd-1;

IF (NEW.ID IS NULL) THEN

SET NEW.ID = @CrntRcrd;

END IF;

SET @PrevHash = (SELECT Hash FROM core_Test WHERE Record=@PrevRcrd);

SET @ClmNms = (SELECT CONCAT('NEW.',GROUP_CONCAT(column_name

ORDER BY ORDINAL_POSITION SEPARATOR ',NEW.'),'')

FROM information_schema.columns

WHERE table_schema = DATABASE()

AND table_name = 'core_Test');

SET @Query = CONCAT("SET @Query2 = CONCAT_WS(',','",@PrevHash,"','", @CrntRcrd, "',", @ClmNms, ");");

PREPARE stmt1 FROM @Query;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

SET NEW.Hash = @Query2;

END

更新/澄清：数据将存储在下表中.

+------------+-----+------+----------------+

| Record (AI)| ID | Data | HASH |

+------------+-----+------+----------------+

| 1 | 1 | ASDF | =DHFBGKJSDFHBG | (Hash Col 1)

| 2 | 2 | NULL | =UEGFRYJKSDFHB | (Hash Col 1 + Col 2)

| 3 | 1 | VBNM | =VKJSZDFVHBFJH | (Hash Col 2 + Col 3)

| 4 | 4 | TYUI | =KDJFGNJBHMNVB | (Hash Col 3 + Col 4)

| 5 | 5 | ZXCV | =SDKVBCVJHBJHB | (Hash Col 4 + Col 5)

+------------+-----+------+----------------+

在每个插入命令上,表将通过将前一行的哈希值映射到整个新行的CONCAT()上,然后重新哈希整个字符串,从而为该行生成哈希值.这将创建哈希值的运行记录,以用于审核目的/在应用程序的另一部分中使用.

我的约束是必须在INSERT之前完成此操作,因为以后不能再更新行.

更新：我目前正在使用以下代码,直到我找到一种将列名动态传递给CONCAT的方法为止：

BEGIN

SET @Record = (

SELECT AUTO_INCREMENT FROM information_schema.TABLES

WHERE TABLE_SCHEMA=DATABASE()

AND TABLE_NAME='core_Test' #

);

SET @PrevRecrd = @Record-1;

IF (new.ID IS NULL) THEN

SET new.ID = @Record;

END IF;

SET @PrevHash = (

SELECT Hash FROM core_Test #

WHERE Record=@PrevRecrd

);

SET new.Hash = SHA1(CONCAT_WS(',',@PrevHash, @Record,

/* --- UPDATE TABLE COLUMN NAMES HERE (EXCLUDE "new.Record" AND "new.Hash") --- */

new.ID, new.Name, new.Data

));

END