如何使用mysql中的动态值设置触发器？

发布于 2024-10-10 11:02:20 字数 424 浏览 0 评论 0原文

我有一个表 A，其中包含某些记录和一些值字段，这些字段会定期更新，然后我有一个从用户那里获取值的表单。现在我必须在该表上设置一个触发器，仅当表 A 的字段值达到用户输入的值时，该触发器才会运行并将数据插入到不同的表 B 中。

像这样的东西 -

DELIMITER $$
 CREATE TRIGGER data_gen
 AFTER UPDATE ON TABLE A
 FOR EACH ROW BEGIN
 IF NEW.field_val ='user-entered-value' THEN
 INSERT INTO TABLE B
 SET ...........;
END IF;
END$$
 DELIMITER ;

现在如何使用用户输入的值设置此触发器？？？我是触发器新手，不知道如何使用动态值设置触发器。我该怎么做？？？请帮忙！！

原文

I have a table A that has certain records with some value fields which gets updated on a regular basis and then i have a form that takes a value from the user. Now i have to set a trigger on that table which runs and insert a data to a different table B only when table A's field-value reaches the user-entered value.

something like this --

DELIMITER $
 CREATE TRIGGER data_gen
 AFTER UPDATE ON TABLE A
 FOR EACH ROW BEGIN
 IF NEW.field_val ='user-entered-value' THEN
 INSERT INTO TABLE B
 SET ...........;
END IF;
END$
 DELIMITER ;

Now how to set this trigger with user-enetered values????
Im new to Triggers and dont have idea to set triggers with the dymanic values.How can i do that???? plz help !!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一人独醉 2024-10-17 11:02:20

我是通过使用存储过程做到的。

触发器就像 -

DELIMITER $
 CREATE TRIGGER tigger_name
 AFTER UPDATE ON table name
 FOR EACH ROW BEGIN
CALL stored_proc(OLD.field1, NEW.field2);
END$
 DELIMITER ;

程序就像 -

DELIMITER $
 DROP PROCEDURE IF EXISTS stored_proc$
 CREATE PROCEDURE stored_proc(IN var1 INT,IN var2 INT)
 BEGIN
DECLARE  no_more_products INT DEFAULT 0;
DECLARE  id_u1 INT;
DECLARE  cur_rows CURSOR FOR 
 SELECT field1 from tableb where field3=var1 and field4=var2;
   DECLARE  CONTINUE HANDLER FOR NOT FOUND 
 SET  no_more_products = 1;
 OPEN  cur_rows;
 FETCH  cur_rows INTO rows;
 REPEAT
 IF  rows > 0 THEN
 INSERT INTO ......... ;
END IF;
 FETCH  cur_rows INTO rows;
 UNTIL  no_more_products = 1
 END REPEAT;
 CLOSE  cur_rows;
END$
 DELIMITER ;

谢谢大家！

I did it by using stored procedures.

Trigger was like --

DELIMITER $
 CREATE TRIGGER tigger_name
 AFTER UPDATE ON table name
 FOR EACH ROW BEGIN
CALL stored_proc(OLD.field1, NEW.field2);
END$
 DELIMITER ;

and procedure was like --

DELIMITER $
 DROP PROCEDURE IF EXISTS stored_proc$
 CREATE PROCEDURE stored_proc(IN var1 INT,IN var2 INT)
 BEGIN
DECLARE  no_more_products INT DEFAULT 0;
DECLARE  id_u1 INT;
DECLARE  cur_rows CURSOR FOR 
 SELECT field1 from tableb where field3=var1 and field4=var2;
   DECLARE  CONTINUE HANDLER FOR NOT FOUND 
 SET  no_more_products = 1;
 OPEN  cur_rows;
 FETCH  cur_rows INTO rows;
 REPEAT
 IF  rows > 0 THEN
 INSERT INTO ......... ;
END IF;
 FETCH  cur_rows INTO rows;
 UNTIL  no_more_products = 1
 END REPEAT;
 CLOSE  cur_rows;
END$
 DELIMITER ;

Thanks all !

回复收藏 0 原文

~没有更多了~