我正在研究数据库分配。我试图创建一个触发器,如果客户在客户表中的平衡超过400,则可以防止用户将值插入宪章表中。触发器编译无错误但行不通。有什么建议么?
CREATE OR REPLACE TRIGGER Unpaid_Balance
BEFORE INSERT ON Charter
FOR EACH ROW
DECLARE
Unpaid DECIMAL;
BEGIN
SELECT Customer_Balance
INTO Unpaid
FROM Customer, Charter
WHERE CUSTOMER.CustomerID = CHARTER.CustomerID;
IF Unpaid > 400 THEN
Raise_application_error(-20003, 'Customer has an unpaid balance of more than
$400');
end if;
end;
您可能想要类似的东西(请参考插入值-:NEW.CustromerID
-这就是您在触发中忘记的(:
CREATE OR REPLACE TRIGGER Unpaid_Balance
BEFORE INSERT ON Charter
FOR EACH ROW
DECLARE
V_Unpaid DECIMAL;
BEGIN
SELECT Customer_Balance
INTO V_Unpaid
FROM Customer
WHERE CUSTOMER.CustomerID = :NEW.CustomerID
;
--
IF V_Unpaid> 400 THEN
Raise_application_error(-20003, 'Customer has an unpaid balance of more than $400');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/
,但我强烈建议您不要将业务逻辑放在触发器中。在您的应用程序中执行此操作。很难用触发器控制流动,因为它们是自动完成的...