mysql> use wendi; Database changed mysql> SHOW TRIGGERS/G; ... *************************** 2. row *************************** Trigger: cl_borrow_before_insert_tigger Event: INSERT Table: cl_borrow Statement: begin set @channel_id = (select channel_id from cl_user where user_id = new.user_id); -- if @channel_id is not null and new.channel_id is null THEN -- update cl_borrow set channel_id=@channel_id where borrow_id = new.borrow_id; -- end if; insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,null,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id); end Timing: BEFORE Created: NULL sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@% character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 3. row *************************** Trigger: cl_borrow_after_insert_trigger Event: INSERT Table: cl_borrow Statement: begin set @channel_id = (select channel_id from cl_user where user_id = new.user_id); -- if @channel_id is not null and new.channel_id is null THEN -- update cl_borrow set channel_id=@channel_id where borrow_id = new.borrow_id; -- end if; insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,null,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id); end Timing: AFTER Created: NULL sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@% character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 4. row *************************** Trigger: cl_borrow_after_update_trigger Event: UPDATE Table: cl_borrow Statement: begin if old.status != new.status then set @channel_id = (select channel_id from cl_user where user_id = new.user_id); insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,old.status,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id); end if; end Timing: AFTER Created: NULL sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@% character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 5. row *************************** Trigger: cl_borrow_status_log Event: INSERT Table: cl_borrow_status_log Statement: BEGIN update cl_borrow set double_audit_user_id = new.audit_user_id,double_audit_time=new.create_time where borrow_id=new.borrow_id ; end Timing: AFTER Created: NULL sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@% character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci ... 11 rows in set (0.00 sec) 如上，總共有11條觸發(fā)器。為了不影響業(yè)務(wù)，我決定先將觸發(fā)器備份，然后將其刪除。
--triggers: Dump triggers for each dumped table. (Defaults to on; use --skip-triggers to disable.) 這個(gè)是默認(rèn)值，mysqldump默認(rèn)會(huì)導(dǎo)出觸發(fā)器。（如果不想備份觸發(fā)器使用--skip-triggers即可）
-R, --routines: Dump stored routines (functions and procedures). 導(dǎo)出存儲(chǔ)過(guò)程以及函數(shù)。
-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that normally is output for each dumped database if --all-databases or --databases is given. 不創(chuàng)建建庫(kù)語(yǔ)句，只對(duì)數(shù)據(jù)進(jìn)行導(dǎo)出。
-d, --no-data No row information. 不導(dǎo)出數(shù)據(jù)，只導(dǎo)出表結(jié)構(gòu)。
2，查看備份內(nèi)容: [root@DB ~]$ less wenditrigger.sql -- MySQL dump 10.13 Distrib 5.6.20, for linux-glibc2.5 (x86_64) ... DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `__test_trigger_update` AFTER INSERT ON `__test` FOR EACH ROW begin if new.user_id=100 THEN update __test set tian='@@@@' where id=new.id; end if; insert into __test2 (id,tian,user_id) values (new.id,new.tian,new.user_id); end */;; ... /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `cl_borrow_before_insert_tigger` BEFORE INSERT ON `cl_borrow` FOR EACH ROW begin set @channel_id = (select channel_id from cl_user where user_id = new.user_id); -- if @channel_id is not null and new.channel_id is null THEN -- update cl_borrow set channel_id=@channel_id where borrow_id = new.borrow_id; -- end if; insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,null,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id); end */;; DELIMITER ; 可以看到觸發(fā)器已經(jīng)備份好了。
... mysql> drop trigger cl_borrow_after_insert_trigger; mysql> drop trigger cl_borrow_after_update_trigger; mysql> drop trigger cl_borrow_status_log; mysql> drop trigger cl_installment_after_insert_trigger; ... 至此，業(yè)務(wù)終于恢復(fù)了。
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_statement trigger_name:觸發(fā)器名稱 trigger_time:觸發(fā)器觸發(fā)時(shí)機(jī)(BEFORE/AFTER) trigger_event: 觸發(fā)事件(INSERT,UPDATE,DELETE) table_name: 建立觸發(fā)器的表名稱 trigger_statement: 觸發(fā)器程序體，可以為單一的SQL語(yǔ)句，也可以是包含BEGIN,END在內(nèi)的多條語(yǔ)句。 FOR EACH ROW: 行級(jí)觸發(fā)
