Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Unanswered: Help With Creating a Trigger

    I've got a script that creates two tables

    TaxReturns:
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SSN NOT NULL CHAR(11)
    ADJGROSSINCOME NUMBER(9)
    INCOMETAXES NUMBER(9)

    and

    TaxRates:
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    MININCOME NOT NULL NUMBER(9)
    MAXINCOME NUMBER(9)
    TAXRATE NUMBER(3,2)

    TaxReturns is empty.

    TaxRates has the following rows:

    MININCOME MAXINCOME TAXRATE
    ---------- ---------- ----------
    0 29999 0
    30000 99999 .2
    100000 249999 .3
    250000 499999 .4
    500000 999999999 .5


    I need to create a Trigger that runs whenever a new tax return is added or the AdjGrossIncome field of a tax return is updated. The trigger should compute the new tax amount based on the appropriate tax rate and place that amount in the IncomeTaxes field.

    I've never made a Trigger before and haven't been able to purchase my databaseII book yet. I could use any help that anyone was willing to give.

    Thanks so much!


    Friend me!
    http://www.myspace.com/bellsonhershoes

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.dbforums.com/query.php?do...FORID:11&hl=en

    http://asktom.oracle.com has many fine coding examples.

    By the way, we don't do homework for folks.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    6
    Thats not what I was wanting (to DO the homework). I was just looking for some help and GUIDANCE.

    Hopefully the links you provided will do that since this forum doesnt. Thanks for the links:

    This is what I have so far:
    CREATE OR REPLACE TRIGGER TAXRETURNS_TRIGGER
    2 AFTER UPDATE ON TaxReturns
    3 FOR EACH ROW

    Correct?

    My major problem is with the PL/SQL block that comes afterwards. I've never used the language and have no book to reference..
    Last edited by Absynthe; 09-06-09 at 13:06.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Last edited by anacedent; 09-06-09 at 23:23.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2009
    Posts
    6
    How does this look?

    Code:
    CREATE OR REPLACE TRIGGER Tax_Return_Trigger
    AFTER INSERT OF TaxReturns_SSN_PK OR UPDATE OF AdjGrossIncome
    ON TaxReturns
    FOR EACH ROW
    
    DECLARE
    v_TaxRate1 := 0.00;
    v_TaxRate2 := 0.20;
    v_TaxRate3 := 0.30;
    v_TaxRate4 := 0.40;
    v_TaxRate5 := 0.50;
    v_NewRate NUMBER;
    
    BEGIN
      IF AdjGrossIncome >= 0 AND AdjGrossIncome <= 29999 THEN
    	v_NewRate := AdjGrossIncome * v_TaxRate1;
    	ELSEIF AdjGrossIncome >= 30000 AND AdjGrossIncome <= 99999 THEN
    	v_NewRate := AdjGrossIncome * v_TaxRate2;
    	ELSEIF AdjGrossIncome >= 100000 AND AdjGrossIncome <= 249999 THEN
    	v_NewRate := AdjGrossIncome * v_TaxRate3;
    	ELSEIF AdjGrossIncome >= 250000 AND AdjGrossIncome <= 499999 THEN
    	v_NewRate := AdjGrossIncome * v_TaxRate4;
    	ELSEIF AdjGrossIncome >= 500000 AND AdjGrossIncome <= 999999999 THEN
    	v_NewRate := AdjGrossIncome * v_TaxRate1;
    				
    			
    	END IF;
    
    INSERT INTO TaxReturns (IncomeTaxes)
    VALUES (v_NewRate);
    END;
     /
    Do I look like Im right?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    close, you cant insert into a table that the trigger is firing on. to set a value you have to use a before trigger and you assign a value like this

    :NEW.IncomeTaxes := v_newRate;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Sep 2009
    Posts
    6
    Im still getting compilation errors??? Anyone see what Im doing wrong?

    Code:
    CREATE OR REPLACE TRIGGER Tax_Return_Trigger
    BEFORE UPDATE OF AdjGrossIncome
    ON TaxReturns
    FOR EACH ROW
    DECLARE
    v_TaxRate1 := 0.00;
    v_TaxRate2 := 0.20;
    v_TaxRate3 := 0.30;
    v_TaxRate4 := 0.40;
    v_TaxRate5 := 0.50;
    v_AdjGrossIncome NUMBER;
    v_NewRate NUMBER;
    BEGIN
    SELECT AdjGrossIncome INTO v_AdjGrossIncome FROM TaxReturns;
    IF :NEW.AdjGrossIncome >= 0 AND :NEW.AdjGrossIncome <= 29999
    THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate1;
    ELSEIF
    :NEW.AdjGrossIncome >= 30000 AND :NEW.AdjGrossIncome <= 99999     THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate2;
    ELSEIF
    :NEW.AdjGrossIncome >= 100000 AND :NEW.AdjGrossIncome <= 249999   THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate3;
    ELSEIF
    :NEW.AdjGrossIncome >= 250000 AND :NEW.AdjGrossIncome <= 499999   THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate4;
    ELSEIF
    :NEW.AdjGrossIncome >= 500000 AND :NEW.AdjGrossIncome <= 999999999
    THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate5;
    END IF;
    INSERT INTO TaxReturns (IncomeTaxes)
    VALUES (v_NewRate);
    END;
    SHOW ERRORS
     /

  8. #8
    Join Date
    Sep 2009
    Posts
    6
    Just fixed ELSEIF to ELSIF...

    Still have: Trigger Created With Compilation Errors

    Any ideas?

  9. #9
    Join Date
    Sep 2009
    Posts
    6
    Code:
    CREATE OR REPLACE TRIGGER Tax_Return_Trigger
    BEFORE UPDATE OF AdjGrossIncome
    ON TaxReturns
    FOR EACH ROW
    DECLARE
    v_TaxRate1 FLOAT := 0.00;
    v_TaxRate2 FLOAT := 0.20;
    v_TaxRate3 FLOAT := 0.30;
    v_TaxRate4 FLOAT := 0.40;
    v_TaxRate5 FLOAT := 0.50;
    v_AdjGrossIncome NUMBER;
    v_NewRate NUMBER;
    BEGIN
    SELECT AdjGrossIncome INTO v_AdjGrossIncome FROM TaxReturns;
    IF :NEW.AdjGrossIncome >= 0 AND :NEW.AdjGrossIncome <= 29999
    THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate1;
    ELSIF
    :NEW.AdjGrossIncome >= 30000 AND :NEW.AdjGrossIncome <= 99999     THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate2;
    ELSIF
    :NEW.AdjGrossIncome >= 100000 AND :NEW.AdjGrossIncome <= 249999   THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate3;
    ELSIF
    :NEW.AdjGrossIncome >= 250000 AND :NEW.AdjGrossIncome <= 499999   THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate4;
    ELSIF
    :NEW.AdjGrossIncome >= 500000 AND :NEW.AdjGrossIncome <= 999999999
    THEN v_NewRate := :NEW.AdjGrossIncome * v_TaxRate5;
    END IF;
    INSERT INTO TaxReturns (IncomeTaxes)
    VALUES (v_NewRate);
    END;
    SHOW ERRORS
     /
    Still having errors but I changed a few things. Guys?

  10. #10
    Join Date
    Feb 2009
    Posts
    62
    I can see a few problems with this:
    Code:
    SELECT AdjGrossIncome INTO v_AdjGrossIncome FROM TaxReturns;
    1) This will error if you've got more than one row in TaxReturns table
    2) It will probably give you a mutating table error anyway, as you're SELECTing from the same table that the trigger is on.
    3) You don't use the v_AdjGrossIncome variable anyway

    Are your AdjGrossIncome guaranteed to be integers? If not then your IF...ELSIF block doesn't catch all the cases.

    At the end, you do this:
    Code:
    INSERT INTO TaxReturns (IncomeTaxes)
    VALUES (v_NewRate);
    1) this will almost certainly raise a mutating table error, as you're trying to insert into the same table that the trigger is on

    2) Your table definintion for TaxReturns has a column SSN that has a not null constraint on it. Unless you've got a before insert trigger to set the SSN column, then you'll need to set the SSN column as part of the insert.

    3) Based on your original post, I suspect that you want to put the Income tax figure into the same row as the one that you updated, in which case you can replace the INSERT with
    Code:
    :NEW.IncomeTaxes := v_newrate;

Posting Permissions

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