oracle - 当我插入的值不满足之前的值时显示 RAISE_APPLICATION_ERROR
问题描述
当我插入的新值未遵循先前的值时,我尝试使用触发器显示 RAISE_APPLICATION_ERROR。例如,我的最新位置在第二位置,当我想插入新位置 10 时,它应该显示 ORA-...“我输入的错误消息”??任何人都可以帮我编辑我的代码??我需要一个选择声明吗?
SET ECHO ON
SET FEEDBACK ON
SET serveroutput on size 2000
CREATE OR REPLACE TRIGGER RowTrigger
Before
INSERT OR UPDATE on POSITION
FOR EACH ROW
declare
u number(2);
begin
select count(P#) into u from POSITION ;
if :new.P# < u
then
RAISE_APPLICATION_ERROR(-20011,'dddd');
end if;
end;
解决方案
好的。有时我花了我写它,下面的工作考虑到:
-The P# is unique, or youll get error in the select.
-I am not taking on consideration the previous data , for example if your P# is 5 and ur inserting 3, then youll not have error.
if you need such thing you can continue developing it because there are some conditions to be met.
-The table already contains data , all you can add a exception NO_data_found and do whatever you want in that case.
这是代码:
drop table ex_employee
/
create table ex_employee (id number(2) null,id1 number(2) NULL,name varchar2(1000) null)
/
drop table POSITION
/
create table POSITION(p# number(2) null)
/
insert into POSITION(p#) values (4)
/
insert into POSITION(p#) values (5)
/
commit
/
CREATE OR REPLACE TRIGGER RowTrigger
FOR INSERT on POSITION compound trigger
prevID number(4);
new_p number(4);
after each row is
begin
new_p := :new.p#;
end after each row;
after statement is
begin
--it gives the previous record;
select PREV_ID into prevID from(SELECt p#, LAG(p#, 1, 0) OVER(ORDER BY p#) as PREV_ID FROM position) where p# = new_p;
-- check if the old data is smaller then the new, you can add more conditions.
if (previd < new_p) then
previd:=previd+1;
end if;
IF (prevID != new_p) THEN
RAISE_APPLICATION_ERROR(-20011, 'ERROR , data not in order or whatever.');
END IF;
end after statement;
end;
/
推荐阅读
- r - 如果数据帧中有 NA,如何在 if / ifelse 中处理 NA
- c# - 以高性能的方式使用 C# 获取所选字符的第一次和最后一次出现之间的字符串
- java - 超级构造函数调用顺序
- java - Spring Boot 多部分文件上传 - 提高性能的技巧
- javascript - 将数据从 php foreach 循环传递到 javascript
- signal-processing - 陀螺仪在高加速度下的非恒定漂移
- certificate - How do e-Signature companies create valid digital signatures
- django - 对相关模型的属性施加约束
- javascript - 我可以在mounted() vuejs 中使用$store.commit() 吗?
- html - 如何为scrapy编写css选择器?