Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    4

    Unanswered: another trigger lol

    i have 2 tables called audit_tab and book_hire:

    CREATE TABLE audit_tab(
    audit_id NUMBER(5) NOT NULL,
    customer_id NUMBER(6),
    start_date DATE NOT NULL,
    book_evaluation NUMBER(1) NOT NULL);

    CREATE TABLE book_hire (
    book_hire_id NUMBER(5) NOT NULL,
    book_id NUMBER(5) CONSTRAINT FK_BOOKID REFERENCES book(book_id),
    customer_id NUMBER(6) CONSTRAINT FK_CUST REFERENCES customer(customer_id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    book_evaluation NUMBER(1) NOT NULL);

    i need a trigger so that everytime a row is entered into the book_hire table, and the book_evaluation of that row is equal to 0, then the customer_id, start_date and book_evaluation details are inserted into the audit_tab table. i tried this but unsuccessful:


    CREATE or REPLACE TRIGGER triggerB
    2 BEFORE INSERT ON book_hire
    3 INSERT INTO audit_tab (book_id, start_date, book_evaluation)
    4 SELECT (book_id, start_date, book_evaluation)
    5 FROM inserted WHERE book_evaluation = 0
    6 end;
    7 /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What made you think SQL Server trigger syntax would work in Oracle?

    Try:

    (1)
    Code:
    IF :NEW.book_evaluation = 0 THEN
      INSERT INTO audit_tab (book_id, start_date, book_evaluation)
      VALUES (:NEW.book_id, :NEW.start_date, :NEW.book_evaluation);
    END IF;
    (2) Reading the documentation Oracle thoughtfully provide like this for example

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •