[資料庫] Navicat for MySQL換種思維解決插入同時更新資料

Eastmount發表於2017-03-12
資料庫MySql

        這篇文章是我的學生在實際專案中遇到的一個案例，在對某張表插入資料過程中，某些特定的欄位需也要進行更新，比如說部門編號在前端插入，而部門名稱在插入時應該自動更新，如果前端設定選擇編號又選擇部門就重複功能了，那麼資料庫怎麼實現呢？
        最早學生想通過觸發器實現，設定一個插入觸發器，插入的同時更新資料，但是問題來了，在同一張表中，觸發器是不能同時插入又更新的。這篇文章主要討論這個問題的解決方法。同時為了加深大家對觸發器的映像，也會在給大家講述些觸發器的知識，起到討論的作用。
        希望文章對你有所幫助，尤其是我的學生和資料庫基礎的博友以及解決實際專案中的類似問題。如果文章中存在錯誤或不足之處，還請海涵~
        推薦前文：
        [資料庫] Navicat for MySQL觸發器更新和插入操作
        [資料庫] Navicat for MySQL事件Event實現資料每日定期操作


一. 觸發器問題

        現在存在兩張表。第一張表為部門表department，核心欄位為unit（學院名稱）、depid（學院編號），如下圖所示：



        第二張表為插入資訊表task，depid表示部門編號、source表示部門名稱，對應表department表的unit欄位。還有插入時間、公告事件等欄位，這裡省略了。


        現在前端有個按鈕，選擇部門編號，但是想通過department部門表同時插入部門名稱，怎麼實現呢？首先，學生想到的是通過觸發器，如下所示：

DROP TRIGGER IF EXISTS `upd_info`;  
create trigger upd_info  
after insert on task for each row   
begin  
    update task,department set source = department.unit  
        where task.depid=department.depid;  
end;
        觸發器設定成功。但是當插入資料時，報錯如下所示：[Err] 1442 - Can't update table 'task' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.


        注意：該錯誤表示如果你在觸發器裡面對剛剛插入的資料進行了 insert/update, 則出現這個問題。因為會造成迴圈的呼叫。解決方法通過set設定值。
        參考地址：http://blog.csdn.net/java2000_net/article/details/3857579




二. 觸發器解決

        現在修改需要通過set來設定值，我修改如下所示：

DROP TRIGGER IF EXISTS `test`;  
create trigger test  
before insert on task for each row   
begin  
	declare dname varchar(20);
	select department.unit into dname from department,task 
		where task.depid=department.depid and task.depid=new.depid;
	set new.source = dname; 
end;
        其中需要注意幾點：
        1.觸發器Before和After的區別。
        (1) before（insert、update）可以對new進行修改；
        (2) after不能對new進行修改；
        (3) 兩者都不能修改old資料；
        (4) 對於Insert語句，只有new是合法的，對於delete語句，只有old合法，而update可以在new和old同時使用。
        否則，在after insert觸發器中使用old，報錯如下：
        [Err] 1363 - There is no OLD row in on INSERT trigger
        2.這裡使用declare定義變數，並且select a into b把查詢結果a賦值到b使用。

        但是執行結果是沒有反應，因為查詢條件中task.depid=new.depid在before insert觸發器中，插入資料前進行查詢，其結果是空的；而使用task.depid=old.depid又不能在insert中使用old。
        比如執行下列SQL語句：
insert task (depid) VALUES('1067105002');
        輸出結果如下所示：


        那怎麼解決呢？解決方法包括：
        1.後臺執行插入語句後，再執行一條Update語句，更新該欄位是最方便的操作；
        2.通過Event實時更新單位名稱，參考前文：
        [資料庫] Navicat for MySQL事件Event實現資料每日定期操作
        但是如果需要通過SQL語句實現呢？那麼我想到的是在使用insert插入過程中呼叫select查詢，這就是我說的換個思維解決該問題。


三. 換個思維解決

        SQL語句程式碼如下：

INSERT INTO task(depid,source) 
	SELECT depid, unit FROM department 
  where department.depid='1067103002';
        執行結果如下所示，可以看到插入資料成功，其中taskid是自增整型，單位名稱新增成功了。後臺把'1067103002'換成對應的Java變數即可連線前端插入。


        同樣，有些時候需要插入條件判斷，如果不存在則插入替代設定唯一性，程式碼為：

INSERT INTO task(depid) 
SELECT '106710511' FROM DUAL WHERE NOT EXISTS(SELECT depid FROM task WHERE depid = '106710511');
        參考文章：
        語法：MySQL中INSERT INTO SELECT的使用 - RoadGY
        MySQL INSERT插入條件判斷：如果不存在則插入

        同時，強烈推薦大家閱讀"老紫竹"老師的資料庫部落格，參考：
        http://blog.csdn.net/java2000_net/article/details/4533826

        最後希望線上筆記對您有所幫助，基礎性文章，如果存在錯誤或不足之處，還請海涵~還是娜麼開心、娜麼美麗。
       (By:Eastmount 2017-03-12 晚上1點   http://blog.csdn.net//eastmount/ )

相關文章