Results 1 to 3 of 3

Thread: Trigger help

  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Exclamation Unanswered: Trigger help

    Hi i am struggling with a trigger in Oracle 9i. i have a statement to which i have to create the SQL for the answer. the statement is:
    A member can have on loan a maximum of 5 items at any one time.

    The table that is mainly included in this is:
    CREATE TABLE hire(
    hireID INTEGER PRIMARY KEY,
    memberID INTEGER NOT NULL,
    stockID INTEGER NOT NULL,
    staffID INTEGER NOT NULL,
    date_hired DATE NOT NULL,
    date_due DATE NOT NULL,
    date_return DATE,
    amount_paid VARCHAR2(6) NOT NULL,
    CONSTRAINT fk_mID FOREIGN KEY (memberID) references member(memberID),
    CONSTRAINT fk_hirestkID FOREIGN KEY (stockID) references stock(stockID),
    CONSTRAINT fk_hirestfID FOREIGN KEY (staffID) references staff(staffID));


    My trigger so far is:
    CREATE OR REPLACE TRIGGER
    triggerb1
    BEFORE INSERT
    ON hire
    FOR EACH ROW
    declare
    memberID;
    BEGIN
    SELECT member.memberID, COUNT (*)
    FROM hire
    WHERE member.memberID ='&memberID'
    AND hire.date_return is null
    GROUP BY (memberID)
    IF (dummy < 5)
    THEN
    INSERT INTO hire
    BEGIN
    PRINT ''
    ELSE
    PRINT 'Member has too many items on loan.'
    END;
    /

    I can't get it to work & i don't know if it is even right.
    Please can you help me
    Emma
    Last edited by squidge08; 04-30-08 at 07:03.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Viewing your attempt, it seems that you lack in basic PL/SQL knowledge. Perhaps you should spend some time reading the PL/SQL User's Guide and Reference book.

    Because, variable should have a datatype; SELECT should have INTO clause; INSERT statement is incomplete; what is BEGIN doing within your IF-THEN-ELSE; what is PRINT?

    There's just too many wrong things here which imply that you have no idea what you are doing (and how to do it). Writing a (working) trigger would be counter-productive because you wouldn't learn anything from it. I believe you should pay more attention during classes (it is some kind of a homework, isn't it?), learn how to use PL/SQL and then do the job.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also, you may NOT query the table you have the trigger on from the trigger. This creates the dreaded mutating trigger error.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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