63 lines
2.3 KiB
SQL
63 lines
2.3 KiB
SQL
USE test;
|
|
create table if not exists account (acct_num int(11), amount decimal(10,2));
|
|
|
|
DROP TRIGGER IF EXISTS tri_set_insert;
|
|
DROP TRIGGER IF EXISTS tri_set_update;
|
|
DROP TRIGGER IF EXISTS tri_set_delete;
|
|
|
|
#############################################################################
|
|
# tri_set_insert
|
|
# this sql use to trigger set new key:value
|
|
# when insert new values to table account
|
|
#############################################################################
|
|
|
|
delimiter |
|
|
|
|
CREATE TRIGGER tri_set_insert AFTER INSERT ON account
|
|
FOR EACH ROW
|
|
BEGIN
|
|
DECLARE done INT DEFAULT 99;
|
|
DECLARE ret INT DEFAULT 99;
|
|
SET @ret=redis_servers_set_v2("127.0.0.1",6379);
|
|
SET @done=redis_command_v2("127.0.0.1",6379,"set",concat("account:",new.acct_num),concat("acct_num:",new.acct_num, " amount:", new.amount));
|
|
END;|
|
|
delimiter ;
|
|
|
|
#############################################################################
|
|
# tri_set_update
|
|
# this sql use to trigger set new key:value
|
|
# when update a row in table account
|
|
#############################################################################
|
|
|
|
delimiter |
|
|
CREATE TRIGGER tri_set_update AFTER UPDATE ON account
|
|
FOR EACH ROW
|
|
BEGIN
|
|
DECLARE done INT DEFAULT 99;
|
|
DECLARE ret INT DEFAULT 99;
|
|
SET @ret=redis_servers_set_v2("127.0.0.1",6379);
|
|
SET @done=redis_command_v2("127.0.0.1",6379,"set",concat("account:",new.acct_num),concat("acct_num:",new.acct_num, " amount:", new.amount));
|
|
END;|
|
|
delimiter ;
|
|
|
|
|
|
#############################################################################
|
|
# tri_set_delete
|
|
# this sql use to trigger delete new key:value
|
|
# when delete a row in table account
|
|
#############################################################################
|
|
|
|
delimiter |
|
|
CREATE TRIGGER tri_set_delete AFTER DELETE ON account
|
|
FOR EACH ROW
|
|
BEGIN
|
|
DECLARE done INT DEFAULT 99;
|
|
DECLARE ret INT DEFAULT 99;
|
|
DECLARE fre INT DEFAULT 99;
|
|
SET @ret=redis_servers_set_v2("127.0.0.1",6379);
|
|
#SET @done=redis_command_v2("127.0.0.1",6379,"set",concat("account:",old.acct_num),concat("del_acct_num:",old.acct_num, " amount:", old.amount));
|
|
SET @done=redis_command_v2("127.0.0.1",6379,"del",concat("account:",old.acct_num),"deleted success.");
|
|
#SET @fre=free_resources(); # means free all redis data
|
|
END; |
|
|
delimiter ;
|