Results 1 to 4 of 4

Thread: Mutating tables

  1. #1
    Join Date
    Oct 2003
    Posts
    11

    Unanswered: Mutating tables

    create table Employee
    (
    fname Varchar2(10),
    minit Varchar2(1),
    lname Varchar2(10),
    ssn Number(10) NOT NULL,
    dbate Date,
    address Varchar2(30),
    sex Varchar2(1),
    Salary Number(6) constraint check_sal CHECK(salary>=0),
    superssn Number(10) NOT NULL DEFAULT 00,
    dno Number(4),
    constraint pssn PRIMARY KEY (ssn),
    constraint fk_mgr FOREIGN KEY (superssn)
    references Employee(ssn) ON DELETE set default ON UPDATE cascade
    );

    create table Project
    (
    pname Varchar2(20),
    pnumber Number(2) NOT NULL,
    plocation Varchar2(10),
    dnum Number(4),
    constraint pn PRIMARY KEY (pnumber),
    constraint fk_dnum FOREIGN KEY (dnum)
    references Department(dnumber) ON DELETE cascade ON UPDATE cascade
    );

    create table Works_On
    (
    essn Number(10),
    pno Number(2),
    hours Number(2),
    constraint pesspn PRIMARY KEY (essn, pno),
    constraint fk_pno FOREIGN KEY (pno)
    references Project( pnumber)
    );


    CREATE OR REPLACE PACKAGE WorksPack
    AS w_essn Number;
    END;
    /

    CREATE OR REPLACE TRIGGER Wk1
    BEFORE INSERT OR UPDATE ON WORKS_ON
    FOR EACH ROW
    BEGIN
    WorksPack.w_essn := :new.essn;
    END;
    /

    CREATE OR REPLACE TRIGGER Wk2
    AFTER INSERT OR UPDATE ON WORKS_ON
    DECLARE
    totalhrs Number(6,2);
    BEGIN
    select sum(hours) into totalhrs
    from Works_On
    where essn=WorksPack.w_essn
    group by essn;
    IF (totalhrs) IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001,'Total hours is null');
    ELSIF (totalhrs < 25 ) THEN
    RAISE_APPLICATION_ERROR(-20002,'Total hours is less than 25 hours');
    ELSIF (totalhrs > 45) THEN
    RAISE_APPLICATION_ERROR(-20003,'Total hours is more than 45 hours');
    ELSE
    dbms_output.put_line('Hours worked complies with company policy');
    END IF;
    END Wk2;
    /


    Is what I have done for the above trigger correct?
    Works_On table references Employee and Project.
    I need to fire the trigger on Works-on when a row is inserted/updated.
    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    Close but not correct.
    You have to consider the possibility of multiple inserted/updated rows in one SQL statement. Therefore, your 'w_essn Number ' variable must be an array!

    Also, you will need a BEFORE trigger to initialize this array.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2003
    Posts
    11
    Incase, I want to stick with insert/update of 1 row at a time, I don't need an array. I would like to keep it as 1 update/insert at a time.
    In that case, what should I do? How should I correct my code?I am getting the following error when I try to update hours-

    ERROR at line 1:
    ORA-04098: trigger 'WORKD_ON11' is invalid and failed re-validation


    Why is the name of the trigger coming as WORKD_ON11 when the name of my triggers are WK1 and WK2?
    Is there a command to show all the triggers I have created so far in my database?
    Thanks,

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    You cannot assume an SQL statement will only insert/update one row!
    What about something like the following:

    update works_on
    set hours=8
    where pno=123;
    ????

    To look at triggers query: ALL_TRIGGERS or USER_TRIGGERS or DBA_TRIGGERS views.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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