触发器被定义为响应insert、update或delete语句而运行的过程。它将在每次执行DML操作时运行。不能将触发器安排为每天运行一次。
我们正在做一个关于微型银行的项目，我们需要在数据库级别计算利率。所以我们使用了程序和触发器。但是触发器需要每天一次执行以执行以下给定过程。你能提供一个创建触发器的解决方案吗。提前感谢
DELIMITER //
create procedure addFDInterestToAccount(in account_number bigint(20))
BEGIN
declare interest float default 0;
declare account_type varchar(10);
declare rate float;
declare savings_account bigint(20);
start transaction;
select balance from fd_account where account_no = account_number into interest;
SELECT plan_id from fd_account where account_no = account_number into account_type;
SELECT saving_account_no from fd_account where account_no = account_number into savings_account;
select interest_rate from fd_plan where plan_id = account_type into rate;
set interest = interest*rate/1200;
insert into transaction (transaction_id, account_no, credit_debit, date_time, amount, type_, agent_id, is_fee) values (null, savings_account, 'debit', now(), interest, 'not_special', null, false);
update account set balance = balance + interest where account_no = savings_account;
commit;
END //
DELIMITER ;
call addFDInterestToAccount(90842311);
就我理解您的需求而言，我认为您可以使用SQL事件。一般语法如下，
CREATE [OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
你可以修改/使用以下代码来实现这一点，
CREATE EVENT example
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO call addFDInterestToAccount(90842311);
有关更多信息，您可以查看此处的链接
还要确保在运行事件之前运行此查询
SET GLOBAL event_scheduler = ON;
来自MySql文档(但适用于所有实现触发器的数据库(：
定义触发器以在语句插入、更新或删除关联表中的行。这些行操作是触发器事件。
相反，您需要查找任务调度程序。每个操作系统都有自己的，或者你可以找到第三方调度软件。