Help With Creating a Trigger

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

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

#31 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 - 08:01 PM

That worked!! That link I just put up is misleading man!! It doesnt show that at all!

Let me covert those and see if it goes through! Thanks SO MUCH! Both of you! Give me a sec :)
Was This Post Helpful? 0
  • +
  • -

#32 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: Help With Creating a Trigger

Posted 10 September 2009 - 08:03 PM

You could also use just NUMBER if precision and scale are not important. Just a tip.
Was This Post Helpful? 0
  • +
  • -

#33 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 - 08:09 PM

CREATE OR REPLACE TRIGGER Tax_Return_Trigger
BEFORE UPDATE OF AdjGrossIncome
ON TaxReturns
FOR EACH ROW
DECLARE
v_AdjGrossIncome NUMBER;
v_TaxRate1  CONSTANT NUMBER(2,2) := 0.00;
v_TaxRate2  CONSTANT NUMBER(2,2) := 0.20;
v_TaxRate3  CONSTANT NUMBER(2,2) := 0.30;
v_TaxRate4  CONSTANT NUMBER(2,2) := 0.40;
v_TaxRate5  CONSTANT NUMBER(2,2) := 0.50;
BEGIN
SELECT AdjGrossIncome INTO v_AdjGrossIncome FROM TaxReturns;

END;
/

so far so good

This worked:

CREATE OR REPLACE TRIGGER Tax_Return_Trigger
BEFORE UPDATE OF AdjGrossIncome
ON TaxReturns
FOR EACH ROW
DECLARE
v_AdjGrossIncome NUMBER;
v_TaxRate1  CONSTANT NUMBER(2,2) := 0.00;
v_TaxRate2  CONSTANT NUMBER(2,2) := 0.20;
v_TaxRate3  CONSTANT NUMBER(2,2) := 0.30;
v_TaxRate4  CONSTANT NUMBER(2,2) := 0.40;
v_TaxRate5  CONSTANT NUMBER(2,2) := 0.50;
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;
INSERT INTO TaxReturns (IncomeTaxes)
VALUES (v_NewRate);
END IF;
END;
/







Let me try it out :) *crosses fingers*
Was This Post Helpful? 0
  • +
  • -

#34 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 - 08:09 PM

i dont think there will be a problem in the if statements but lets check it out
Was This Post Helpful? 0
  • +
  • -

#35 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: Help With Creating a Trigger

Posted 10 September 2009 - 08:11 PM

As Stef from the Goonies would say at a time like this (with my small edit) "I feel like I am babysitting a programmer but I am not getting paid"

OR....

As Amy Lee from Evanescence would say.... "Call me when you are sober"

;)

This post has been edited by Martyr2: 10 September 2009 - 08:12 PM

Was This Post Helpful? 0
  • +
  • -

#36 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 - 08:13 PM

Shit

It created the Trigger correctly but the trigger does nothing..that sucks

Dammit ...

Going to look it over now

This post has been edited by absynthe: 10 September 2009 - 08:14 PM

Was This Post Helpful? 0
  • +
  • -

#37 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: Help With Creating a Trigger

Posted 10 September 2009 - 08:16 PM

You selected into v_AdjGrossIncome, so use that in your comparisons! What is with all the :NEW for? hehe

EDIT: Well, I guess you could use :old and just ignore the old select at the start, but I think both will accomplish the job.

This post has been edited by Martyr2: 10 September 2009 - 08:22 PM

Was This Post Helpful? 0
  • +
  • -

#38 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 - 08:20 PM

maybe you actually need to pull the trigger :P
Was This Post Helpful? 0
  • +
  • -

#39 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 - 08:28 PM

View PostMartyr2, on 10 Sep, 2009 - 09:16 PM, said:

You selected into v_AdjGrossIncome, so use that in your comparisons! What is with all the :NEW for? hehe


You have to use :NEW from what the instructor said? haha! Im so confused :)
Was This Post Helpful? 0
  • +
  • -

#40 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: Help With Creating a Trigger

Posted 10 September 2009 - 08:30 PM

Ok, well one thing I have been meaning to ask is that when we started this thread we were talking about AFTER UPDATE. But right now we are doing BEFORE UPDATE. So I am not sure which you are doing here. I think now you are on the right track though and this thread has taken a lot longer than it should have. hehehe

Edit: Either way, you are in a lot better shape than you were.

This post has been edited by Martyr2: 10 September 2009 - 08:34 PM

Was This Post Helpful? 0
  • +
  • -

#41 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 - 08:36 PM

haha!! SO TRUE. Well it started as a After and ended up this way :) Im so confused! Im getting the book as soon as I can. That will help. Bookless makes it so hard! :) Thank you so much Martyr2. You've helped me understand how to break it down and make it easier. Gotta turn it in now. Midnight deadline :) You're an angel!
Was This Post Helpful? 0
  • +
  • -

#42 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 - 09:05 PM

My head is swimming. UGH. Thanks again guys! Turned it in, the professor can see I tried even if the logic is off. Just a homework grade anyhoo. 100 points out of 5000 so blah. I do want to figure it out and will continue to fix it. Im sure once I see it up in class it will be clear. When I used the variable in the comparisson it came back as an error which is odd. Each and every time. If I didn't use it in the figures the trigger compiled. The error I think is in the Insert. Seems like I remember something about not being able to insert into a table a trigger is firing on? Dunno..Ill figure it out though.

Thanks again to both of you! It took DAYS AND DAYS to get help with it. It just so happened it came on the last night. If I can ever become a database guru Im going to start patrolling this area of DIC. Not too many people here really. Everyone likes their JAVA too much! haha!

KUDOS to both of you :)

haha! This is a hot topic in database...wonder if thats happened before?

I thanked you both :) Ill brag on you as usual to those around me!

This post has been edited by absynthe: 10 September 2009 - 09:07 PM

Was This Post Helpful? 0
  • +
  • -

#43 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 - 09:08 PM

that's coz Java rocks :D..so does Haskell :)
Was This Post Helpful? 0
  • +
  • -

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