Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    18

    Unanswered: Oracle SQL *Plus Trigger Problem

    Hi all,

    I am trying to apply the following trigger to my database (emp):

    CREATE OR REPLACE TRIGGER salcheck_tg
    BEFORE UPDATE OF sal on emp
    BEGIN
    IF sum(sal) > 30000 THEN
    RAISE_APPLICATION_ERROR(-20100,'Salary Sum is now too high');
    END IF;
    END;


    The idea is to ensure that the sum of the column sal will not be allowed to total over 30000. When compiled I get a compiled but with errors message. I dont have access to the error message so will add it later but maybe someone can see where I'm going wrong ?

    Thanks for your time in advance

    Much appreciated !

    Greg

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You mean, the sum of all salaries for all employees must not exceed 30000?

    In that case:

    CREATE OR REPLACE TRIGGER salcheck_tg
    AFTER UPDATE OF sal on emp
    DECLARE
    v_sumsal NUMBER;
    BEGIN
    SELECT sum(sal) INTO v_sumsal FROM emp;
    IF v_sumsal > 30000 THEN
    RAISE_APPLICATION_ERROR(-20100,'Salary Sum is now too high');
    END IF;
    END;

  3. #3
    Join Date
    Oct 2005
    Posts
    18
    hia,

    The trigger compiles correctly now but when i insert a record with a sal that creates a sal sum over 30000 no error is shown serveroutput is set on, so any ideas whats wrong ? im using sql*plus

    Thanks

    Greg

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your trigger is explicitly defined to fire only on UPDATEs. To handle inserts also:

    AFTER INSERT OR UPDATE OF sal on emp

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    Isn't this a case for a mutating trigger? Oracle won't let you query rows on the same table within the trigger. Maybe a table-level check constraint could enforce the same rule.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by urquel
    Isn't this a case for a mutating trigger? Oracle won't let you query rows on the same table within the trigger. Maybe a table-level check constraint could enforce the same rule.
    No, the mutating tables issue only applies to FOR EACH ROW triggers.

    Oracle doesn't support table-level check constraints, only row-level.

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    Thanks Tony. I never knew about the FOR EACH ROW and the mutation. I do find it surprising that Oracle will not support table constraints.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by urquel
    I do find it surprising that Oracle will not support table constraints.
    I wish they did. But then again, does anybody? At the moment the best solution I know of is to simulate table constraints via materialized views

Posting Permissions

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