插入 if 语句之前的 Oracle 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19719843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle trigger before insert if statement
提问by Pindo
I've got the following trigger to check if there are available beds in a table called WARDbefore the user tries to input a value, if not then an error will be shown.
我有以下触发器来检查WARD在用户尝试输入值之前调用的表中是否有可用床位，如果没有，则将显示错误。
create or replace
trigger checkBeds
before insert or update on INPATIENT_STAY for
each row
Begin
select BEDSAVAIL from WARD where wardnum=:new.WARDNUM;
if(BEDSAVAIL <=0) then
DBMS_OUTPUT.PUT_LINE('There are no beds available.');
end if;
END;
when this is run I get the following error:
运行时出现以下错误：
Error(2,42): PLS-00049: bad bind variable 'NEW.WARDNUM'
错误（2,42）：PLS-00049：错误的绑定变量'NEW.WARDNUM'
WARDis linked to INPATIENT_STAYby a foreign key in INPATIENT_STAYcalled WARDNUM
WARD被调用INPATIENT_STAY的外键链接到INPATIENT_STAYWARDNUM
Here's the structure of the WARDtable
这是WARD表的结构
CREATE TABLE WARD
(
WARDNUM VARCHAR2(20 BYTE) NOT NULL
, NAME VARCHAR2(40 BYTE)
, LOCATION VARCHAR2(20 BYTE)
, TOTALBEDS NUMBER
, TELEXTENTION VARCHAR2(20 BYTE)
, BEDSAVAIL NUMBER
, CONSTRAINT WARD_PK PRIMARY KEY
(
WARDNUM
));
here's the structure of the INPATIENT_STAYtable
这是INPATIENT_STAY表的结构
CREATE TABLE INPATIENT_STAY
(
INPATIENTID VARCHAR2(20 BYTE) NOT NULL
, PATIENTNUMBER VARCHAR2(20 BYTE)
, WADNUM VARCHAR2(20 BYTE)
, DATEONWAITINGLIST DATE
, EXPECTEDDURATION VARCHAR2(20 BYTE)
, EXPECTEDLEAVEDATE DATE
, DATEPLACEDINWARD DATE
, REQWARD VARCHAR2(20 BYTE)
, DATELEFT DATE
, CONSTRAINT INPATIENT_STAY_PK PRIMARY KEY
(
INPATIENTID
)
);
How do I resolve this?
我该如何解决？
回答by Dba
You need to select the value of BEDSAVAILto a local variable and compare with it.
您需要选择BEDSAVAIL局部变量的值并与它进行比较。
Try like this,
试试这样，
CREATE OR REPLACE
TRIGGER checkbeds
BEFORE INSERT OR UPDATE ON inpatient_stay
FOR EACH ROW
DECLARE
l_bedsavail ward.bedsavail%TYPE;
BEGIN
SELECT bedsavail
INTO l_bedsavail
FROM ward
WHERE wardnum=:NEW.wadnum;
IF(l_bedsavail <= 0) THEN
raise_application_error( -20001, 'There are no beds available.'); --to restrict the insetion`.
END IF;
END;
/
Edited:
编辑：
There is a mistake in the column_name in INPATIENT_STAY. The name is WADNUMinstead of WARDNUM. Please rename the column name in the table or made changes in the Trigger.
中的 column_name 有错误INPATIENT_STAY。名称是WADNUM而不是WARDNUM。请重命名表中的列名或在触发器中进行更改。
I have updated my answer with column_name WADNUM.
我已经用 column_name 更新了我的答案WADNUM。
WHERE wardnum=:NEW.wadnum;
To restrict the insertion or updation on table INPATIENT_STAT, you can raise an application error like,
要限制 table 的插入或更新INPATIENT_STAT，您可以引发应用程序错误，例如，
raise_application_error( -20001, 'There are no beds available.')
