[MYSQL]觸發器應用11/10

 1.觸發器的查詢:
    show triggers;
    select action_statement from information_schema.triggers  where trigger_name='ins_sum' and trigger_schema='demodb';/*笨笨的寫法
   show triggers like 'account';/*account為table名稱
   show triggers like 'acc%';/*acc為table名稱中的相關字母
   show triggers like '%t%';/*t為table名稱中的一個字母

2.觸發器的刪除:
   drop trigger demodb.ins_sum;

3.觸發器的建立:
   語法如下:
   Create trigger 觸發器名稱   /*觸發器名稱字符最多為64個
   {before | after}  /*觸發時間
   {insert | update | delete}   /*觸發事件
   on table名稱 /*觸發表名
   for each row  /*觸發間隔
     觸發器SQL語句;
  p.s.1.要更新或刪除的字段/欄位用new | old .欄位名來表示
       2.在for each row後的觸發器SQL語句若有多個可用begin ...end
       3.同一張表不能存在2個如befor insert/before update/before delete/after insert/after update/after delete觸發器

4.觸發器實例:
   I.有關test1,test2,test3,test4
    A.創建table:test1,test2,test3,test4
     CREATE TABLE test1(a1 INT);
     CREATE TABLE test2(a2 INT);
     CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
     CREATE TABLE test4(
                                       a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                       b4 INT DEFAULT 0);
    B.創建testref觸發器
     DELIMITER |

   CREATE TRIGGER testref BEFORE INSERT ON test1
    FOR EACH ROW
     BEGIN
       INSERT INTO test2 SET a2 = NEW.a1;
       DELETE FROM test3 WHERE a3 = NEW.a1; 
      UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
     END;|

   DELIMITER ;
   C.執行相關SQL時會觸發
   INSERT INTO test3 (a3) VALUES
    (NULL), (NULL), (NULL), (NULL), (NULL),
    (NULL), (NULL), (NULL), (NULL), (NULL);

  INSERT INTO test4 (a4) VALUES
     (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
  INSERT INTO test1 VALUES 
    (1),(3),(1),(7),(1),(8),(4),(4);
   D.查詢結果
      select * from test1;
      select * from test2;
      select * from test3;
      select * from test4;
  II.有關account
   A.創建account
     CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
   B.創建2個觸發器
     CREATE TRIGGER ins_sum BEFORE INSERT ON account
       For each row set @sum=@sum+New.amount;
    CREATE TRIGGER upd_check BEFORE UPDATE ON account
     for each row
       begin 
          if new.amount<0 then
              set new.amount=0;
          elseif new.amount>100 then
              set new.cmount=100;
          end if;
        end;
  C.執行相關SQL查詢時觸發
    SET @sum = 0;
    Insert into account values(137,14.98),(141,1937.50),(97,-100.00);/*觸發ins_sum
    select @sun as Total';
   Update account set amount=20 where acct_num=137;/*觸發upd_check
  

待續。。。