oracle 触发器对元素进行计数

kcrjzv8t 于 2023-04-11 发布在 Oracle

关注(0)|答案(1)|浏览(120)

有一个MODEL表，如下所示

create table MODEL (
model_name VARCHAR(20),
model_year NUMBER,
consumption VARCHAR(6) NOT NULL,
Component component_ty_nt,
Distributor distributor_ty_nt,
Car car_ty_nt,
constraint t_pk primary key (Model_Name, Model_Year))
nested table Component store as component_ty_nt_TAB
nested table Distributor store as distributor_ty_nt_TAB
nested table Car store as car_ty_nt_TAB

Component_ty

Create or replace type Component_ty as object (
Code VARCHAR(10),
Component_Description VARCHAR(100),
Component_Type VARCHAR(10))
NOT FINAL;

具有component_type属性，该属性可以是'body'、'engine'或'equipment'
每次我在这个表上执行INSERT时，我都需要一个触发器来检查至少有3个组件被插入，其中1个必须是'engine'类型，另一个必须是'body'类型。
对于元素的数量，此触发器返回以下错误

create or replace trigger number_of_components 
after insert on Model
for each row
declare
componentNumber NUMBER;
BEGIN 
select 
c.component_number INTO componentNumber from model m
outer apply (select count(*) as component_number from table(component)) c
where m.model_name = :new.model_name AND m.model_year = :new.model_year;
if componentNumber < 3 then
    raise_application_error(-20061, 'Not enough components');
end if;
end;

ora-04091: table DB_EXAM.Model it mutating, trigger/function may not see it
ora-06512: at "DB_EXAM.NUMBER_OF_COMPONENT", line 4
ora-04088: error during execution of trigger'DB_EXAM.NUMBER_OF_COMPONENT'

我做错了什么？我如何在同一个触发器中检查类型上的约束？还是一个新的触发器更好？

oracle

来源：https://stackoverflow.com/questions/75980145/trigger-to-count-elements

1条答案

按热度按时间

twh00eeo1#

不要尝试从表中进行选择。使用包含每个插入行的:NEW记录和集合的COUNT属性（对于用户定义的异常，错误代码需要在-20000和-20999之间）。然后要检查组件的类型，只需循环每个组件并检查类型：

CREATE TRIGGER number_of_components 
AFTER INSERT ON Model
  FOR EACH ROW
DECLARE
  has_engine PLS_INTEGER := 0;
  has_body   PLS_INTEGER := 0;
BEGIN 
  IF :NEW.component.COUNT < 3 THEN
    raise_application_error(-20061, 'Not enough components');
  END IF;

  FOR i IN 1 .. :NEW.component.COUNT LOOP
    IF :NEW.component(i).component_type = 'engine' THEN
      has_engine := 1;
    ELSIF :NEW.component(i).component_type = 'body' THEN
      has_body := 1;
    END IF;
  END LOOP;
  IF has_engine = 0 OR has_body = 0 THEN
    raise_application_error(-20061, 'Does not have engine and body.');
  END IF;
END;
/

然后对于类型/表：

Create or replace type Component_ty as object (
  Code                  VARCHAR(10),
  Component_Description VARCHAR(100),
  Component_Type        VARCHAR(10)
)
NOT FINAL;

CREATE TYPE component_ty_nt IS TABLE OF component_ty;

create table MODEL (
  model_name VARCHAR(20),
  model_year NUMBER,
  consumption VARCHAR(6) NOT NULL,
  Component component_ty_nt,
  --Distributor distributor_ty_nt,
  --Car car_ty_nt,
  constraint t_pk primary key (Model_Name, Model_Year)
)
nested table Component store as component_ty_nt_TAB
--nested table Distributor store as distributor_ty_nt_TAB
--nested table Car store as car_ty_nt_TAB
;

如果您用途：

INSERT INTO model (model_name, model_year, consumption, component)
VALUES ('A', 1900, 'X', component_ty_nt());

那么错误是：

ORA-20061: Not enough components
ORA-06512: at "FIDDLE_CVZJKWFQKTBCNENJZAKH.NUMBER_OF_COMPONENTS", line 3
ORA-04088: error during execution of trigger 'FIDDLE_CVZJKWFQKTBCNENJZAKH.NUMBER_OF_COMPONENTS'

但如果您用途：

INSERT INTO model (model_name, model_year, consumption, component)
VALUES ('A', 1900, 'X', component_ty_nt(
  component_ty('A', 'A', 'body'),
  component_ty('B', 'B', 'engine'),
  component_ty('C', 'C', 'equipment')
));

那就成功了
要回答第二个问题，不要使用触发器，向嵌套表添加一个约束：

ALTER TABLE component_ty_nt_tab 
  ADD CONSTRAINT component_type__chk
  CHECK (component_type IN ('body', 'engine', 'equipment'));

fiddle

赞(0）回复(0）举报 2023-04-11

我来回答

oracle 触发器对元素进行计数

1条答案

相关问题

热门标签

最新问答