oracle 用于检查值是否出现的触发器

有一个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时，我都需要一个触发器来检查类型的出现情况，如果Boyd！= 1或'Engine'！= 1，则返回错误。

CREATE OR REPLACE TRIGGER check_engine_and_body
BEFORE INSERT ON model
FOR EACH ROW
DECLARE
engineCounter NUMBER;
bodyCounter NUMBER;
BEGIN
  FOR i IN 1..:new.Component.COUNT LOOP
    IF deref(:new.Component(i)).Component_Type = 'Engine' THEN
      engineCounter := engineCounter + 1;
    ELSIF deref(:new.Component(i)).Component_Type = 'Body' THEN
      bodyCounter := bodyCounter + 1;
    END IF;
  END LOOP;

  IF engineCounter != 1 OR bodyCounter != 1 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Incorrect occurrences for component_type attribute');
  END IF;
END;

但我得到错误：

PL/SQL: Statement ignore 
PLS-00306: wrong number or types of arguments in call to 'DEREF'

如何修复？

我还没有弄清楚为什么你的触发器会失败，但是使用SQL检查（而不是PL/SQL）。
我假设你有类型和表：

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 AS TABLE OF REF component_ty;

CREATE TABLE components OF component_ty;

create table MODEL (
  model_name VARCHAR(20),
  model_year NUMBER,
  Component component_ty_nt,
  constraint t_pk primary key (Model_Name, Model_Year)
)
nested table Component store as component_ty_nt_TAB;

ALTER TABLE component_ty_nt_TAB ADD SCOPE FOR (COLUMN_VALUE) IS components;

你可以使用trigger：

CREATE OR REPLACE TRIGGER check_engine_and_body
  BEFORE INSERT ON model
  FOR EACH ROW
DECLARE
  counter PLS_INTEGER;
BEGIN
  SELECT 1
  INTO   counter
  FROM   TABLE(:NEW.Component) c
  WHERE  DEREF(c.COLUMN_VALUE).component_type IN ('Engine', 'Body')
  HAVING COUNT(CASE DEREF(c.COLUMN_VALUE).component_type WHEN 'Engine' THEN 1 END) = 1
  AND    COUNT(CASE DEREF(c.COLUMN_VALUE).component_type WHEN 'Body'   THEN 1 END) = 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20000, 'Incorrect occurrences for component_type attribute');
END;
/

对于样本数据：

INSERT INTO components 
  SELECT component_ty('A', 'AAA', 'Engine') FROM DUAL UNION ALL
  SELECT component_ty('B', 'BBB', 'Body')   FROM DUAL UNION ALL
  SELECT component_ty('C', 'CCC', 'Other')  FROM DUAL;

然后：

INSERT INTO model (model_name, model_year, component)
VALUES ('A', 1, component_ty_nt(
  (SELECT REF(c) FROM components c WHERE code = 'A'),
  (SELECT REF(c) FROM components c WHERE code = 'B')
));

INSERT INTO model (model_name, model_year, component)
VALUES ('E', 1, component_ty_nt(
  (SELECT REF(c) FROM components c WHERE code = 'A'),
  (SELECT REF(c) FROM components c WHERE code = 'B'),
  (SELECT REF(c) FROM components c WHERE code = 'C')
));

作品及：

INSERT INTO model (model_name, model_year, component)
VALUES ('B', 1, component_ty_nt(
  (SELECT REF(c) FROM components c WHERE code = 'A'),
  (SELECT REF(c) FROM components c WHERE code = 'A')
));

INSERT INTO model (model_name, model_year, component)
VALUES ('C', 1, component_ty_nt(
  (SELECT REF(c) FROM components c WHERE code = 'B'),
  (SELECT REF(c) FROM components c WHERE code = 'B')
));

INSERT INTO model (model_name, model_year, component)
VALUES ('D', 1, component_ty_nt(
  (SELECT REF(c) FROM components c WHERE code = 'C'),
  (SELECT REF(c) FROM components c WHERE code = 'C')
));

INSERT INTO model (model_name, model_year, component)
VALUES ('F', 1, component_ty_nt());

INSERT INTO model (model_name, model_year, component)
VALUES ('G', 1, NULL);

INSERT INTO model (model_name, model_year, component)
VALUES ('H', 1, component_ty_nt(
  (SELECT REF(c) FROM components c WHERE code = 'A'),
  (SELECT REF(c) FROM components c WHERE code = 'A'),
  (SELECT REF(c) FROM components c WHERE code = 'B'),
  (SELECT REF(c) FROM components c WHERE code = 'C')
));

每个失败，并返回错误：

ORA-20000: Incorrect occurrences for component_type attribute
ORA-06512: at "FIDDLE_FGDYNUZQHDRDVPAYKGUH.CHECK_ENGINE_AND_BODY", line 12
ORA-04088: error during execution of trigger 'FIDDLE_FGDYNUZQHDRDVPAYKGUH.CHECK_ENGINE_AND_BODY'

fiddle

oracle 用于检查值是否出现的触发器

1条答案

相关问题

热门标签

最新问答