Триггер Oracle SQL вставка новых записей на основе значения столбца insert

Я пытаюсь создать триггер для таблицы Oracle. Вот требования:
У меня есть две таблицы книги, копии (книги и копии имеют отношение 1 к n. Каждая книга может иметь от 0 до n экземпляров)



Таблица Книг: 



CREATE TABLE Book

  (

    book_id      INTEGER NOT NULL ,

    isbn         VARCHAR2 (20) NOT NULL,

    publisher_id INTEGER NOT NULL ,

    tittle       VARCHAR2 (100) NOT NULL ,

    cat_id       INTEGER NOT NULL ,

    no_of_copies INTEGER NOT NULL ,

    ....

    CONSTRAINT isbn_unique UNIQUE (isbn),

    CONSTRAINT shelf_letter_unique UNIQUE (shelf_letter, call_number) 

  ) ;


Копирует Таблицу 



CREATE TABLE Copies

  (

    copy_id     INTEGER NOT NULL ,

    book_id     INTEGER NOT NULL ,

    copy_number INTEGER NOT NULL,

    constraint copy_number_unique unique(book_id,copy_number)

  ) ;


Триггер (при обновлении, редактировании таблицы книги) должен добавить соответствующие записи копии в таблицу копий. Таким образом, если вставка в таблицу книг имеет Book. no_of_copies как 5, то пять новых записей должны быть вставлены в копии стол..

1022   3  

3 ответов:

Это довольно долго, но на самом деле довольно просто.

Протестировано на установке Oracle 10gR2.

Таблица: 

CREATE TABLE books
(
  book_id INTEGER NOT NULL,
  no_of_copies INTEGER NOT NULL,
  CONSTRAINT pk_book_id PRIMARY KEY (book_id)
);

CREATE TABLE copies
(
  book_id INTEGER NOT NULL,
  copy_no INTEGER NOT NULL,
  CONSTRAINT fk_book_id FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE
);

Затем триггер: 

CREATE TRIGGER tri_books_add
  AFTER INSERT ON books
  FOR EACH ROW
DECLARE
  num INTEGER:=1;
BEGIN
  IF :new.no_of_copies>0 THEN
    WHILE num<=:new.no_of_copies LOOP
      INSERT INTO copies (book_id,copy_no) VALUES (:new.book_id,num);
      num:=num+1;
    END LOOP;
  END IF;
END;
/

CREATE TRIGGER tri_books_edit
  BEFORE UPDATE ON books
  FOR EACH ROW
DECLARE
  num INTEGER:=1;
BEGIN
  IF :new.no_of_copies<:old.no_of_copies THEN
    RAISE_APPLICATION_ERROR(-20001,'Decrease of copy number prohibited.');
  ELSIF :new.no_of_copies>:old.no_of_copies THEN
    SELECT max(copy_no)+1 INTO num FROM copies WHERE book_id=:old.book_id;
    WHILE num<=:new.no_of_copies LOOP
      INSERT INTO copies (book_id,copy_no) VALUES (:old.book_id,num);
      num:=num+1;
    END LOOP;
  END IF;
END;
/

Что делает триггер:

Причина, по которой я разделяю книги, вставляющие и редактирующие в два триггера, заключается в том, что я использовал foreign key ограничение , поэтому after insert потребуется для вставки (поправьте меня, если я ошибаюсь в этом).

Затем я выполняю некоторый тест: 

INSERT INTO books (book_id,no_of_copies) VALUES (1,3);
INSERT INTO books (book_id,no_of_copies) VALUES (2,5);

SQL> select * from copies;

   BOOK_ID    COPY_NO
---------- ----------
         1          1
         1          2
         1          3
         2          1
         2          2
         2          3
         2          4
         2          5

8 rows selected.

SQL> update books set no_of_copies=5 where book_id=1;

1 row updated.

SQL> select * from copies;

   BOOK_ID    COPY_NO
---------- ----------
         1          1
         1          2
         1          3
         2          1
         2          2
         2          3
         2          4
         2          5
         1          4
         1          5

10 rows selected.

SQL> update books set no_of_copies=3 where book_id=1;
update books set no_of_copies=3 where book_id=1
       *
ERROR at line 1:
ORA-20001: Decrease of copy number prohibited.
ORA-06512: at "LINEQZ.TRI_BOOKS_EDIT", line 5
ORA-04088: error during execution of trigger 'LINEQZ.TRI_BOOKS_EDIT'

(я, кажется, не могу заставить sqlfiddle работать с триггерами, поэтому никаких онлайн-демонстраций, извините.)

1  
2017-05-23 14:50:02

Приведенный ниже код работает для вставки и обновления в табличной книге. Он вставлял бы строки в таблицу копий только тогда, когда либо новая строка вставляется в Книгу таблиц, либо существующая строка в книге таблиц обновляется с no_of_copies больше, чем ее текущее значение.

Создание таблицы: 

CREATE TABLE Book
(
book_id      INTEGER NOT NULL ,
isbn         VARCHAR2 (20) NOT NULL,
publisher_id INTEGER NOT NULL ,
tittle       VARCHAR2 (100) NOT NULL ,
cat_id       INTEGER NOT NULL ,
no_of_copies INTEGER NOT NULL ,
CONSTRAINT isbn_unique UNIQUE (isbn) 
) ;

CREATE TABLE Copies
(
copy_id     INTEGER NOT NULL ,
book_id     INTEGER NOT NULL ,
copy_number INTEGER NOT NULL,
constraint copy_number_unique unique(book_id,copy_number)
);

CREATE SEQUENCE COPY_SEQ
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
NOCACHE;

Триггер: 

CREATE OR REPLACE TRIGGER TR_TEST
BEFORE INSERT OR UPDATE ON BOOK
FOR EACH ROW
DECLARE
V_CURR_COPIES    NUMBER;
V_COUNT          NUMBER := 0;

BEGIN

 IF  :NEW.NO_OF_COPIES > NVL(:OLD.NO_OF_COPIES, 0) THEN
     SELECT COUNT(1)
     INTO   V_CURR_COPIES   --# of rows in COPIES table for a particular book.
     FROM   COPIES C
     WHERE  C.BOOK_ID = :NEW.BOOK_ID;

     WHILE  V_COUNT < :NEW.NO_OF_COPIES - V_CURR_COPIES
     LOOP
         INSERT INTO COPIES
         (
         COPY_ID,
         BOOK_ID,
         COPY_NUMBER
         )
         SELECT COPY_SEQ.NEXTVAL,
                :NEW.BOOK_ID,
                V_COUNT + V_CURR_COPIES + 1
         FROM   DUAL;

         V_COUNT := V_COUNT + 1;
     END LOOP;
 END IF;
END;

Тестирование: 

INSERT INTO BOOK
VALUES (1, 'ABCDEF', 2, 'TEST BOOK', 1, 3);

UPDATE BOOK B
SET    B.NO_OF_COPIES = 4
WHERE  B.BOOK_ID = 1;
2  
2013-09-30 09:03:29 
create or replace 
trigger BOOK_TRIGGER 
AFTER INSERT ON BOOK 
FOR EACH ROW 
DECLARE L_COPIES NUMBER:= :NEW.NO_OF_COPIES;
BEGIN
     FOR I IN 1..5
     LOOP
          INSERT
          INTO COPIES
               (
                    COPY_ID,
                    BOOK_ID,
                    COPY_NUMBER
               )
               VALUES
               (
                    1, -- your copy sequence
                    :new.book_id,
                    i
               );
     END LOOP;
END;
1  
2013-09-30 08:35:55

Comments

    Ничего не найдено.
Click here to cancel reply.