sqloracle-對來自不同表的2列的約束

我設計了一個機票預訂系統。我想添加一個約束，使您可以插入的機票數量少于飛行飛機的座位數量。

假設我插入了一個帶有 10 個座位的飛機的航班。我只能插入該特定航班的 10 張機票。否則，應顯示錯誤訊息。

我嘗試使用航班號上的計數功能來觸發。

CREATE OR REPLACE TRIGGER trg_ticket_BRIU 
    BEFORE INSERT OR UPDATE ON Ticket 
    FOR EACH ROW 
DECLARE
    l_numberofseats flight.numberofseats%type;
BEGIN
    select numberofseats into l_numberofseats
    from flight
    where flightnumber=:new.flightnumber;

    IF :new.count(flightnumber) > l_numberofseats
    THEN
        raise_application_error(-2000, 'Not enough seats');
    END IF;
END;

但我收到這個錯誤

Trigger TRG_TICKET_BRIU compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
8/5       PLS-00049: bad bind variable 'NEW.COUNT'
Errors: check compiler log

uj5u.com熱心網友回復：

就個人而言，我會添加一個AIRCRAFT和一個SEAT表：

CREATE TABLE aircraft (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT aircraft__id__pk PRIMARY KEY,
  tail_number  VARCHAR2(6)
               NOT NULL
               CONSTRAINT aircraft__tn__u UNIQUE
               CONSTRAINT aircraft__tn_chk CHECK(
                 REGEXP_LIKE(
                   tail_number,
                   '[A-Z]\d{1,5}|[A-Z]\d{1,4}[A-Z]|[A-Z]\d{1,3}[A-Z]{2}'
                 )
               ),
  manufacturer VARCHAR2(20)
               NOT NULL,
  model        VARCHAR2(20)
               NOT NULL,
  airline_id   CONSTRAINT aircraft__aid__fk REFERENCES airline(airline_id)
               NOT NULL
);

CREATE TABLE seat (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT seat__id__pk PRIMARY KEY,
  aircraft_id  CONSTRAINT seat__aid__fk REFERENCES aircraft(id)
               NOT NULL,
  seat_row     VARCHAR2(3)
               NOT NULL,
  seat_column  NUMBER
               NOT NULL,
  CONSTRAINT seat__aid_r_c__u UNIQUE (aircraft_id, seat_row, seat_column)
);

然后你的flight表將參考aircraft：

CREATE TABLE flight (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT flight__id__pk PRIMARY KEY,
  aircraft_id  CONSTRAINT flight__aid__fk REFERENCES aircraft(id)
               NOT NULL
  -- ...
);

并且ticket將參考 aflight和 a seat：

CREATE TABLE ticket (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT ticket__id__pk PRIMARY KEY,
  flight_id    CONSTRAINT ticket__fid__fk REFERENCES flight(id)
               NOT NULL,
  seat_id      CONSTRAINT ticket__sid__fk REFERENCES seat(id)
               NOT NULL,
  -- ...
  CONSTRAINT ticket__fid_sid__u UNIQUE (flight_id, seat_id)
);

那么你永遠不能出售一個seat不存在aircraft的票，并且不需要計算最大數量的機票并將其與座位進行比較（并且seat已經添加了可以在機票上顯示的飛機位置等屬性）。

那么你需要的只是確保參考一致性，對于 a ticket， theflight和 theseat是相同的aircraft；這可以通過觸發器完成：

CREATE TRIGGER ticket_check_seat_on_flight
  BEFORE INSERT OR UPDATE ON ticket
  FOR EACH ROW
DECLARE
  is_valid NUMBER(1);
BEGIN
  SELECT 1
  INTO   is_valid
  FROM   flight f
         INNER JOIN seat s
         ON (f.aircraft_id = s.aircraft_id)
  WHERE  f.id = :NEW.flight_id
  AND    s.id = :NEW.seat_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(
      -20000,
      'Flight and seat are on different aircraft.'
    );
END;
/

db<>在這里擺弄

uj5u.com熱心網友回復：

您可以使用AFTER STATEMENT觸發器：

CREATE TRIGGER ticket__check_number_of_seats
  AFTER INSERT OR UPDATE OR DELETE ON ticket
DECLARE
  is_invalid NUMBER(1,0);
BEGIN
  SELECT 1
  INTO   is_invalid
  FROM   flight f
         INNER JOIN (
           SELECT flight_id,
                  COUNT(*) AS tickets_sold
           FROM   ticket
           GROUP BY flight_id
         ) t
         ON f.id = t.flight_id
  WHERE  t.tickets_sold > f.number_of_seats;
  
  RAISE_APPLICATION_ERROR(
    -20000,
    'Too many tickets sold for flight.'
  );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

可以通過使用復合觸發器將每一行的flight_id值整理到一個集合中，然后在陳述句之后只檢查這些航班的機票數量，從而提高效率；但是，我會將該擴展作為 OP 的練習。

db<>在這里擺弄

uj5u.com熱心網友回復：

正如其他人指出的那樣，沒有 :new.count 列。這是因為 :new （和 :old）創建了一個偽行，其中包含與表定義完全相同的列。此外，您將得到一個Mutating例外，因為您需要在flight_numberfrom 中計數tickets。但是，由于這是導致觸發器觸發的表，因此您無法參考它。那么該怎么做：創建一個復合觸發器和一個支持型別（嵌套表）。在其中使用該after row部分來捕獲已處理的 flight_numbers。然后在該after statement部分中，您可以選擇每個航班的機票數量。如果該計數> 0，則引發您的例外。（見演示）

create type flight_tickets_ntt
         is table of integer;

create or replace trigger trg_ticket_ciu
   for update or insert on tickets
       compound trigger
       
    l_flights  flight_tickets_ntt := flight_tickets_ntt(); 
    
    after each row is 
    begin 
        if :new.flight_number not member of l_flights then 
            l_flights.extend ;
            l_flights(l_flights.count) := :new.flight_number; 
        end if; 
    end after each row; 
    
    after statement is
        l_flight_cnt flight.flight_number%type;
    begin 
         select count(*)
           into l_flight_cnt
           from flight f 
          where f.number_of_seats < 
                ( select count(*)
                    from tickets t 
                   where t.flight_number in 
                         ( select * 
                             from table (l_flights)
                         ) 
                );  
       
          if l_flight_cnt > 0 then 
             raise_application_error(-20000, 'Not enough seats');
          end if; 
           
    end after statement; 
end trg_ticket_ciu; 

您還需要處理一個問題：如果更新更改了航班號或（缺少列）航班資料，會發生什么情況。

標籤：sql 甲骨文

上一篇：匹配嵌套查詢中的多列

下一篇：我想做一些列的內部連接，但如果可能，仍然顯示其他列