[absynthe]

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!

[absynthe]

Aargh

Anything? Anyone? Yooo hoooo!

Please help!

[absynthe]

What I have to date:

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;


*edit* I think this is right?

This post has been edited by absynthe: 06 September 2009 - 10:00 PM

[absynthe]

Hey boo Whats up with ya?

Now I just need the If Then for the different salaries etc I think

This post has been edited by absynthe: 06 September 2009 - 09:45 PM

[absynthe]

This is what I have:

ANY IDEAS? ANYONE? IS IT WRONG?

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;
 /

This post has been edited by absynthe: 06 September 2009 - 10:56 PM

[absynthe]

Man the Database section at DIC is weak in help. Suprisingly WEAK

[absynthe]

CREATE OR REPLACE TRIGGER Tax_Return_Trigger
AFTER 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
 / 

What are the compilation errors?

[mostyfriedman]

its not ELSEIF its ELSIF

[absynthe]

Shit you're right(thank you luv!!) but I still have Compilation Errors..

Any ideas? Anyone?

This post has been edited by absynthe: 10 September 2009 - 05:58 PM

[mostyfriedman]

can you post the errors you are receiving and i will try to help out

[absynthe]

It just says: Trigger Created With Compilation Errors

Thats all it says unfortunately. When I update the table it doesnt calculate the value so the trigger is not working at all

[absynthe]

Triggers

Great site that may help you help me

Pics in it for ya

[mostyfriedman]

you should declare the type of the variables like

v_TaxRate1 FLOAT := 0.00;

[absynthe]

Still got the same error

Thinking about chucking this computer out the window

[Martyr2]

Well do a process of elimination here. Take out the whole if else statement and see if the trigger still compiles with errors. If it does, then you have an error that isn't that whole big if statement. I would first remove the if else statement and insert the variable v_AdjGrossIncome straight into TaxReturns. See if that goes through smoothly first and then you can tackle the if else statement.