oracle - Oracle Apex 第二次触发表抛出变异错误
问题描述
我有如下结构的表 p_it_people 并在其上创建了触发器。
CREATE TABLE "P_IT_PEOPLE"
( "PERSON_ID" NUMBER NOT NULL ENABLE,
"PERSON_NAME" VARCHAR2(255) NOT NULL ENABLE,
"PERSON_EMAIL" VARCHAR2(255) NOT NULL ENABLE,
"PERSON_ROLE" VARCHAR2(30) NOT NULL ENABLE,
"USERNAME" VARCHAR2(255) NOT NULL ENABLE,
"ASSIGNED_DEPT" NUMBER,
"CREATED_ON" DATE NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
"MODIFIED_ON" DATE,
"MODIFIED_BY" VARCHAR2(255),
"PERSON_PASSWORD" VARCHAR2(100),
"APPROVER" VARCHAR2(50),
CONSTRAINT "P_IT_PEOPLE_PK" PRIMARY KEY ("PERSON_ID")
USING INDEX ENABLE,
CONSTRAINT "P_IT_PEOPLE_NAME_UK" UNIQUE ("PERSON_NAME")
USING INDEX ENABLE,
CONSTRAINT "P_IT_PEOPLE_USERNAME_UK" UNIQUE ("USERNAME")
表上的现有触发器:
CREATE OR REPLACE EDITIONABLE TRIGGER "P_IT_PEOPLE_BIU"
before insert or update on p_it_people
for each row
begin
if inserting then
if :NEW.PERSON_ID is null then
:NEW.PERSON_ID := it_api.gen_pk;
end if;
:NEW.CREATED_ON := sysdate;
:NEW.CREATED_BY := nvl(v('APP_USER'),USER);
end if;
if updating then
:NEW.MODIFIED_ON := sysdate;
:NEW.MODIFIED_BY := nvl(v('APP_USER'),USER);
end if;
end;
除此之外,我想创建另一个触发器,它会在创建新条目时发送电子邮件。我部署了这个触发器:
CREATE OR REPLACE EDITIONABLE TRIGGER "P_IT_ISSUES_AIU_NEW_PASSWORD"
AFTER
insert on P_IT_PEOPLE
for each row
DECLARE
v_person_id number;
v_username varchar2(50);
v_Email varchar2(255);
Begin
select person_id,username,person_email into v_person_id,v_username,v_email from p_it_people where person_id=v_person_id;
APEX_MAIL.SEND(
p_to => v_email,
p_from => v_email,
p_body => 'Your account has been created ' ||chr(10)||
' Username'|| v_username||chr(10)||
' Password'||v_username ,
p_subj => 'New User');
end;
现在,当我尝试插入行时,它会抛出错误-p_it_people 正在变异。我该如何反击?
解决方案
您正在从同一个表中选择,因为行级触发器正在触发 - 正是导致“表正在变异”的原因。但在这种情况下,你甚至不需要。只需像这样使用 :NEW 伪记录:
CREATE OR REPLACE EDITIONABLE TRIGGER "P_IT_ISSUES_AIU_NEW_PASSWORD"
AFTER
insert on P_IT_PEOPLE
for each row
Begin
APEX_MAIL.SEND(
p_to => :NEW.person_email,
p_from => :NEW.person_email ,
p_body => 'Your account has been created ' ||chr(10)||
' Username'|| :NEW.username||chr(10)||
' Password'||:NEW.username ,
p_subj => 'New User');
end;
推荐阅读
- python - 气流 - 无法获取日志文件
- sql-server - Entity Framework Plus - 使用 InlcudeFilter 进行多级失败
- intellij-idea - 在搜索结果对话框中更改 IntelliJ IDEA 选择项目背景颜色
- flutter - 是否可以一次调用多个 API
- arrays - 调整数组以容纳不同类型或空白项目?
- java - 如何使视频仅从按钮开始?
- docker - Docker compose volume:执行 docker-compose up 时将文件夹从 docker 容器复制到主机
- c++ - 我需要做什么才能让 Boost.Beast HTTP 解析器找到正文的结尾?
- python - Python - 如何从条目小部件中分配数字以列出和汇总它们?
- math - 创建最长路径 > x 的随机图