MYSQL 触发器实例。

DROP TRIGGER S041_ADD;
CREATE TRIGGER `S041_ADD` AFTER INSERT ON `s041` FOR EACH ROW
BEGIN

DECLARE ID_VAR INT;
DECLARE S010_ID INT;
--  SELECT S011 IS NULL，RETURN 1 NOT NULL，0 IS NULL
SELECT   COUNT(ID)   INTO  ID_VAR  FROM   S011  WHERE  ID = NEW.ID AND PNO=NEW.PNO AND CNO=NEW.CNO AND SNO=NEW.SNO GROUP BY ID,PNO,CNO;
-- SELECT S010 IS NULL，RETURN 1 NOT NULL，0  IS NULL
SELECT COUNT(ID)   INTO  S010_ID    FROM  S010  WHERE    ID=NEW.ID AND PNO=NEW.PNO AND CNO=NEW.CNO AND SNO=NEW.SNO GROUP BY ID,PNO,CNO;

-- IF ID_VAR >0 ， UPDATE S011
IF  ID_VAR > 0 THEN
    UPDATE S011 SET QTY = QTY + NEW.QTY,FRISTINPUTDATE = NOW()
WHERE  ID = NEW.ID AND PNO = NEW.PNO AND CNO = NEW.CNO  AND SNO = NEW.SNO;

END IF;

-- IF ID_VAR<1,INSERT S011
IF ID_VAR < 1 THEN
    INSERT  INTO  S011 (ID,PNO,CNO,SNO,INPUTID,PURID,STOCKID,MSTATUS,QTY,CREATEDATE)
VALUES (NEW.ID,NEW.PNO,NEW.CNO,NEW.SNO,NEW.INPUTID,NEW.PURID,NEW.MSTATUS,NEW.QTY,NOW());

END IF;

--  END UPDATE AND INSERT

--  BEGIN S010
IF S010_ID>0 THEN

UPDATE s010 SET QTY=QTY+NEW.QTY  WHERE ROOTSTOCKID=NEW.STOCKID AND  PNO=NEW.PNO AND CNO=NEW.CNO AND SNO=NEW.SNO AND ID=NEW.ID;

END IF;

IF S010_ID<1 THEN
    UPDATE s010 SET QTY=QTY+NEW.QTY  WHERE ROOTSTOCKID=NEW.STOCKID AND  PNO=NEW.PNO AND CNO=NEW.CNO AND SNO=NEW.SNO AND ID=NEW.ID;

ELSE
           INSERT INTO S010(ID,rootstockid,PNO,CNO,SNO,QTY,CREATE_DATE)
    VALUES (NEW.ID,NEW.STOCKID,NEW.PNO,NEW.CNO,NEW.SNO,NEW.QTY,NOW());

END IF;




END;