我需要将此sql触发器转换为sqlite和我的sql triggger ...帮助我 [英] i need to convert this sql trigger to sqlite and my sql triggger ...help me

问题描述

======================= TRIGGER ========= ==================

创建或替换TRIGGER qtydecrement

插入PORDER后

对于每一行

开始

DECLARE

金额int;

选择数量INTO QUANTITY FROM MATERIALHOUSE WHERE NEW。 r_materialid =：Materialno;

如果金额< =：NEW.R_QTY

那么

RAISE（-20001，'OUT OF STACK'）;

ELSE

UPDATE MATERIALHOUSE SET Quantity = Quantity-：NEW.R_QTY WHERE new.materialid = materialno;

END IF;

结束;

/

=========================== =========== final =========================

创建或替换触发计费

插入PORDER后
每行


DECLARE

价格编号（10）;

SALARY NUMBER（10）;

开始

从物料房选择价格以及MNUM =：NEW.RM_NUM;

SELECT从EMPLO获得薪酬YEE WHERE EMPID =：NEW.R_EMPID;

INSERT INTO BILL（BILLNO，B_MNUM，B_QTY，DOP，TOTPRICE，B_CMPNUM，B_VEHINO，B_EMPID）VALUES（：NEW.ORDERNO，：NEW.RM_NUM，： NEW.R_QTY，SYSDATE，0，：NEW.R_CMPNUM，：NEW.R_VEHINO，：NEW.R_EMPID）;

UPDATE BILL SET TOTPRICE =：NEW.R_QTY * PRICE + SALARY BERENO =：NEW .ORDERNO;

END;

============================= ==============================

=======================TRIGGER ===========================
CREATE OR REPLACE TRIGGER qtydecrement
AFTER INSERT ON PORDER
FOR EACH ROW
begin
DECLARE
amount int;
SELECT Quantity INTO QUANTITY FROM MATERIALHOUSE WHERE new.r_materialid=:Materialno;
IF amount<=:NEW.R_QTY
THEN
RAISE(-20001,'OUT OF STACK');
ELSE
UPDATE MATERIALHOUSE SET Quantity=Quantity-:NEW.R_QTY WHERE new.materialid=materialno;
END IF;
END;
/
======================================final=========================
CREATE OR REPLACE TRIGGER BILLING
AFTER INSERT ON PORDER
FOR EACH ROW
DECLARE
PRICE NUMBER(10);
SALARY NUMBER(10);
begin
SELECT RATE INTO PRICE FROM MATERIALHOUSE WHERE MNUM=:NEW.RM_NUM;
SELECT WAGES INTO SALARY FROM EMPLOYEE WHERE EMPID=:NEW.R_EMPID;
INSERT INTO BILL(BILLNO,B_MNUM,B_QTY,DOP,TOTPRICE,B_CMPNUM,B_VEHINO,B_EMPID)VALUES(:NEW.ORDERNO,:NEW.RM_NUM,:NEW.R_QTY,SYSDATE,0,:NEW.R_CMPNUM,:NEW.R_VEHINO,:NEW.R_EMPID);
UPDATE BILL SET TOTPRICE=:NEW.R_QTY*PRICE+SALARY WHERE BILLNO=:NEW.ORDERNO;
END;
===========================================================

推荐答案

参见 SQLite查询语言：CREATE TRIGGER [ ^ ]。

这篇关于我需要将此sql触发器转换为sqlite和我的sql triggger ...帮助我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！