MYSQL 트리거 중첩트리거

트리거 중첩트리거 
 
use shopdb;
 
 
create table orderTbl    - -구매 테이블
(orderNo Int AUTO_INCREMENT PRIMARY key,
userID VARCHAR(5),
prodName VARCHAR(5),
orderamount int
);
 
 
 
CREATE TABLE prodTbl  --물품테이블
( prodName VARCHAR(5),
    ACCOUNT int);
    
    
 create table deliverTbl(    --배송테이블 
 deliverNo INT AUTO_INCREMENT PRIMARY KEY,
 PRODNAME VARCHAR(5),
 ACCOUNT INT UNIQUE
 );  
 
 
 INSERT INTO prodTbl values('apple',100);
 INSERT INTO prodTbl values('tomat',100);
 INSERT INTO prodTbl values('pine',100);
 
 --  주문테이블에 insert 되면 상품테이블에 update가 발생하는 트리거 
 
 drop trigger if exists orderTrg;
 DELIMITER //
 CREATE TRIGGER orderTrg
    AFTER INSERT
    ON orderTbl
   FOR EACH ROW
    BEGIN
        UPDATE prodTbl SET ACCOUNT = ACCOUNT - NEW.orderamount
        where PRODNAME = new.PRODNAME;
 END //
 DELIMITER ;
 
 -- 상품테이블에 update가 발생하면 발송테이블에 insert가 발생
 
 DROP TRIGGER IF EXISTS prodTrg;
 DELIMITER //
 CREATE TRIGGER prodTrg
 AFTER UPDATE
 ON prodtbl
 FOR EACH ROW
 
 BEGIN
    DECLARE orderamount int;
    
    set orderamount = old.account - new.account;
    INSERT into deliverTbl(prodName,account) values(new.prodName,orderamount);
    
END //
DELIMITER ;
 
 
 INSERT INTO orderTbl VALUES(NULL,'JONE','apple',10);
 
 
 select * from prodTbl;
 select * from deliverTbl;