这里我使用触发器自动增加字段'Project_Id‘的前缀字符串。首先,我将检索最后一个记录并将其存储到variable.After中,该记录的子字符串被存储到另一个变量中,然后转换为Integer。
delimiter //
CREATE OR REPLACE TRIGGER tg_project_master
BEFORE INSERT ON project_master
FOR EACH ROW
BEGIN
DECLARE selectid VARCHAR(20);
DECLARE selid VARCHAR(20);
DECLARE autoi INT DEFAULT 0;
SET selectid := (select Project_id from project_master order by Project_id desc limit 1);
SET selid := SUBSTRING_INDEX(selectid,'O',-1);
SET autoi := 1+ CAST(@selectid AS INT);
INSERT INTO project_master SET NEW.Project_id = CONCAT('PRJ',@autoi);
END; //
DELIMITER;在用1递增值之后,我将插入表(Project_master)中。
这就是我要犯的错误。
错误代码1064,SQL状态42000:您的SQL语法有错误;请检查与您的MySQL服务器版本对应的手册,以便在第1行第2列的project_master插入之前使用接近“触发器tg_project_master”的正确语法。
发布于 2015-03-18 11:39:32
delimiter //
CREATE TRIGGER tg_project_master
BEFORE INSERT ON project_master
FOR EACH ROW
BEGIN
DECLARE selectid INT DEFAULT 0;
SET selectid := (select CONVERT(SUBSTRING_INDEX(Project_id,'O',-1),UNSIGNED INTEGER) AS num from project_master order by Project_id desc limit 1);
SET selectid := selectid + 1;
SET NEW.Project_id = CONCAT('PRO',selectid);
END; //
DELIMITER;问题是,当不需要@符号时,我正在使用它,并且使用CAST()函数,而不是转换()函数。现在,当我按以下格式插入新记录时,将插入我的自动递增前缀Id。
发布于 2015-03-18 09:43:13
根据文档,您不能CREATE OR REPLACE触发器,而只能CREATE触发器。以下几点也是如此:
DROP TRIGGER IF EXISTS tg_project_master;
delimiter //
CREATE TRIGGER tg_project_master
BEFORE INSERT ON project_master
FOR EACH ROW
....
END; //
DELIMITER;https://stackoverflow.com/questions/29118392
复制相似问题