好的,因此我需要创建一个插入后触发器,该触发器需要使用if elseif和another elseif。if,elseif,elseif需要基于从其他3个数据库表获得的查询数据。我想知道是否有一种方法可以不必在开始时运行所有三个查询。
BEGIN
SET @t3 = (SELECT team_id FROM team_trans WHERE key1 = NEW.trans_id LIMIT 1);
SET @t2 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.STORE_ID AND team_assignments.type = 1);
SET @t1 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.CUSTOMER_ID AND team_assignments.type = 0);
IF (@t3) THEN
INSERT INTO team_trans (team_id, trans_id, type, misc_id) VALUES (@t3, NEW.id, 0, 1 );
ELSEIF (@t1) THEN
INSERT INTO team_trans (team_id, trans_id, type, misc_id) VALUES (@t1, NEW.id, 0, 1 );
ELSEIF (@t2) THEN
INSERT INTO team_trans (team_id, trans_id, type, misc_id) VALUES (@t2, NEW.id, 0, 1 );
END IF;
END
一个选项是:
...
BEGIN
/*
SET @t3 = (SELECT team_id FROM team_trans WHERE key1 = NEW.trans_id LIMIT 1)//
SET @t2 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.STORE_ID AND team_assignments.type = 1)//
SET @t1 = (SELECT team_id FROM team_assignments WHERE team_assignments.misc_id = NEW.CUSTOMER_ID AND team_assignments.type = 0)//
*/
IF (SELECT @t3 := team_id
FROM team_trans
WHERE key1 = NEW.trans_id
LIMIT 1) THEN
INSERT INTO team_trans (team_id, trans_id, type, misc_id)
VALUES
(@t3, NEW.id, 0, 1);
ELSEIF (SELECT @t1 := team_id
FROM team_assignments
WHERE team_assignments.misc_id = NEW.CUSTOMER_ID AND team_assignments.type = 0) THEN
INSERT INTO team_trans (team_id, trans_id, type, misc_id)
VALUES
(@t1, NEW.id, 0, 1);
ELSEIF (SELECT @t2 := team_id
FROM team_assignments
WHERE team_assignments.misc_id = NEW.STORE_ID AND team_assignments.type = 1) THEN
INSERT INTO team_trans (team_id, trans_id, type, misc_id)
VALUES
(@t2, NEW.id, 0, 1);
END IF;
END//
...
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句