Help With Creating a Trigger

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

42 Replies - 1708 Views - Last Post: 10 September 2009 - 09:08 PM Rate Topic: -----

#1 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Help With Creating a Trigger

Posted 05 September 2009 - 02:24 PM

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!

Is This A Good Question/Topic? 0
  • +

Replies To: Help With Creating a Trigger

#2 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 06 September 2009 - 08:16 AM

Aargh ;)

Anything? Anyone? Yooo hoooo!

Please help!
Was This Post Helpful? 0
  • +
  • -

#3 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 06 September 2009 - 09:04 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 06 September 2009 - 09:44 PM

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

Was This Post Helpful? 0
  • +
  • -

#5 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 06 September 2009 - 10:38 PM

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

Was This Post Helpful? 0
  • +
  • -

#6 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 12:23 PM

Man the Database section at DIC is weak in help. Suprisingly WEAK
Was This Post Helpful? 0
  • +
  • -

#7 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 05:28 PM

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?
Was This Post Helpful? 0
  • +
  • -

#8 mostyfriedman  Icon User is offline

  • The Algorithmi
  • member icon

Reputation: 727
  • View blog
  • Posts: 4,473
  • Joined: 24-October 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 05:32 PM

its not ELSEIF its ELSIF
Was This Post Helpful? 0
  • +
  • -

#9 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 05:58 PM

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

Was This Post Helpful? 0
  • +
  • -

#10 mostyfriedman  Icon User is offline

  • The Algorithmi
  • member icon

Reputation: 727
  • View blog
  • Posts: 4,473
  • Joined: 24-October 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 05:59 PM

can you post the errors you are receiving and i will try to help out
Was This Post Helpful? 0
  • +
  • -

#11 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 06:03 PM

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 :(
Was This Post Helpful? 0
  • +
  • -

#12 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 06:17 PM

Triggers

Great site that may help you help me :)

Pics in it for ya ;)
Was This Post Helpful? 0
  • +
  • -

#13 mostyfriedman  Icon User is offline

  • The Algorithmi
  • member icon

Reputation: 727
  • View blog
  • Posts: 4,473
  • Joined: 24-October 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 06:21 PM

you should declare the type of the variables like
v_TaxRate1 FLOAT := 0.00;


Was This Post Helpful? 0
  • +
  • -

#14 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help With Creating a Trigger

Posted 10 September 2009 - 06:28 PM

Still got the same error :(

Thinking about chucking this computer out the window :)
Was This Post Helpful? 0
  • +
  • -

#15 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,126
  • Joined: 18-April 07

Re: Help With Creating a Trigger

Posted 10 September 2009 - 07:00 PM

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.

:)
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3