我有两个表TABLE_A和TABLE_B。当将某些东西插入到TABLE_A中时,会触发一个触发器,该触发器还将数据插入到TABLE_B中。TABLE_A具有一个ID列,该ID列使用序列填充。然后,此ID也将插入TABLE_B中。这是整个DDL:
CREATE TABLE "TABLE_A"
( "ID" NUMBER(8,0) NOT NULL ENABLE,
"COLUMN1" NUMBER(8,0) NOT NULL ENABLE,
"COLUMN2" NUMBER(4,0) NOT NULL ENABLE
)
/
CREATE TABLE "TABLE_B"
( "ID" NUMBER(8,0) NOT NULL ENABLE,
"COLUMN1" NUMBER(8,0) NOT NULL ENABLE,
"COLUMN2" NUMBER(4,0) NOT NULL ENABLE
)
/
CREATE UNIQUE INDEX "AID_PK" ON "TABLE_A" ("ID")
/
ALTER TABLE "TABLE_A" ADD CONSTRAINT "AID_PK" PRIMARY KEY ("ID")
USING INDEX "AID_PK" ENABLE
/
create or replace TRIGGER my_trigger
BEFORE INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TABLE_B(
ID,
COLUMN1,
COLUMN2)
VALUES(
:new.ID,
:new.COLUMN1,
:new.COLUMN2);
END IF;
END;
/
ALTER TRIGGER "my_trigger" ENABLE
/
CREATE SEQUENCE "MY_SEQ" MINVALUE 1 MAXVALUE 999999999999999 INCREMENT BY 1 START WITH 5002 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL;
/
CREATE OR REPLACE EDITIONABLE TRIGGER "MYSEQ_SEQ_IOT"
before insert on table_a
for each row
begin
select MY_SEQ.nextval into :new.id from dual;
end;
/
ALTER TRIGGER "MYSEQ_SEQ_IOT" ENABLE
/
现在,当我运行以下语句时:
INSERT INTO PER_ART(
COLUMN1,
COLUMN2
)
VALUES(
1111111,
2222222);
我收到此错误:
ORA-01400: cannot insert NULL into ("TABLE_B"."ID")
ORA-06512: at "my_trigger", line 7
ORA-04088: error during execution of trigger 'my_trigger'
为什么在应填充序列时ID为null?
您的两个触发器触发的顺序是不确定的。我认为,最好的解决方案是只使用一个触发器:
create or replace TRIGGER my_trigger
BEFORE INSERT ON TABLE_A
FOR EACH ROW
BEGIN
select MY_SEQ.nextval
into :new.id
from dual;
INSERT INTO TABLE_B(
ID,
COLUMN1,
COLUMN2)
VALUES(
:new.ID, -- Or use MY_SEQ.curreval
:new.COLUMN1,
:new.COLUMN2);
END;
如果由于某种原因必须有两个触发器,则可以使用CREATE TRIGGER语句的FOLLOWS和PRECEDES子句控制它们的触发顺序。有关控制触发顺序的详细信息,请参阅文档。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句