MySQL觸發器學習

標籤：表資料   插入   系統資料表   資料   學習   image   mysql   tiny   table   

簡介

MySQL從5.0.2版本開始支援觸發器的功能。觸發器是與表有關的資料庫物件，在滿足定義條件時觸發，並執行觸發器中定義的語句集合。

建立觸發器

文法：

　　CREATE TRIGGER trigger_name trigger_time trigger_event

　　　　ON tb_name FOR EACH ROW trigger_stmt

根據文法來示範如何建立一個觸發器，首先建立2張表：student 和 student_demo

-- CREATE TABLE studentCREATE TABLE `student` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(255) NOT NULL,  `age` tinyint(4) NOT NULL,  `gender` enum(‘女‘,‘男‘) NOT NULL,  `address` varchar(255) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- CREATE TABLE student_demoCREATE TABLE `student_demo` (  `id` int(11) NOT NULL,  `name` varchar(255) NOT NULL,  `address` varchar(255) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

現在假設在表student新增一條記錄後，student_demo也需新增一條記錄，根據此需求來示範觸發器的建立

-- CREATE TRIGGER ins_studentCREATE TRIGGER ins_student AFTER INSERT ON student FOR EACH ROWBEGIN    INSERT INTO student_demo (id, name, address)    VALUES     (        new.id,        new.name,         new.address     );END;

現在為student表建立了AFTER INSERT的觸發器，在向student表插入資料時，student_demo也會插入相應的記錄

可以看到，在向student表中新增一個名為"韓梅梅"的學生資訊時，student_demo也新增了其對應資訊

對於INSERT INTO...ON DUPLICATE KEY UPDATE語句，觸發觸發器的順序有所不同。對student表分別建立BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE，插入記錄觀察結果。

 1 -- 建立表tri_test 2 CREATE TABLE tri_test(id INT auto_increment, note VARCHAR(20), PRIMARY KEY (id)) 3  4 -- 建立before insert觸發器 5 CREATE TRIGGER ins_student_bef BEFORE INSERT ON student FOR EACH ROW 6 BEGIN 7     INSERT INTO tri_test (note) 8 VALUES 9     (‘before insert‘);10 END;11 12 -- 建立after insert觸發器13 CREATE TRIGGER ins_student_aft AFTER INSERT ON student FOR EACH ROW14 BEGIN15     INSERT INTO tri_test (note)16 VALUES17     (‘after insert‘);18 END;19 20 -- 建立before update觸發器21 CREATE TRIGGER upd_student_bef BEFORE UPDATE ON student FOR EACH ROW22 BEGIN23     INSERT INTO tri_test (note)24 VALUES25     (‘before update‘);26 END;27 28 -- 建立after update觸發器29 CREATE TRIGGER upd_student_aft AFTER UPDATE ON student FOR EACH ROW30 BEGIN31     INSERT INTO tri_test (note)32 VALUES33     (‘after update‘);34 END;

View Code

 現在student表中已有一條記錄

現在對id=3的記錄，插入資料

INSERT INTO studentVALUES    (        3,        ‘李雷‘,        20,        ‘男‘,        ‘河北石家莊‘    ) ON DUPLICATE KEY UPDATE NAME = ‘update record‘

表student和tri_test中的資料為：

對於有重複記錄、需要進行UPDATE的INSERT操作，觸發器出發的順序是BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE

如果是插入新的不重複的資料

INSERT INTO studentVALUES    (        6,        ‘大衛‘,        20,        ‘男‘,        ‘河北保定‘    ) ON DUPLICATE KEY UPDATE NAME = ‘update record‘

則表資料為

觸發器的順序為BEFORE INSERT、AFTER INSERT

 

刪除觸發器

一次可刪除一個觸發器，如果沒有指定則預設為當前資料庫。文法如下：

DROP TRIGGER [schema_name.]trigger_name

例如，要刪除觸發器ins_student，可以執行

 

查看觸發器SHOW TRIGGERS

可以通過SHOW TRIGGERS命令來查看觸發器的狀態、文法等資訊，此命令查詢的是所有觸發器的資訊

查詢系統資料表

通過查詢系統資料表中的information_schema.triggers表，可以查詢指定觸發器的資訊

查詢指定觸發器"ins_student"的資訊

 

MySQL觸發器學習