Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Help (sql)
Trigger Help [message #431824] Thu, 19 November 2009 11:34 Go to next message
blammo04
Messages: 5
Registered: November 2009
Junior Member
Posted - 11/19/2009 : 11:37:17
--------------------------------------------------------------------------------

I am trying to use a trigger to generate a late fee from two dates. If the return date is passed the planned return date then I need to subtract those days and multiply by 100 and then UPDATE the latefee column with the new latefee amount charged.

I keep getting a compilation error with this.....

SQL> CREATE OR REPLACE TRIGGER latefree
2 BEFORE INSERT OR UPDATE OF actualreturn,plannedreturn ON charters
3 FOR EACH ROW
4 DECLARE late number(8,2);
5 BEGIN
6 IF(:new.actualreturn) > plannedreturn THEN
7 late=(actualreturn-plannedreturn)*100;
8 UPDATE Charters
9 SET latefee = late;
10 END IF;
11 END;
12
13 /

Warning: Trigger created with compilation errors.



IF anyone could help I would greatly appreciate it. THANKS.
Re: Trigger Help [message #431826 is a reply to message #431824] Thu, 19 November 2009 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"plannedreturn" and other mentioned columns come from either :new or :old, so you have to prefix them by this.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Trigger Help [message #431828 is a reply to message #431824] Thu, 19 November 2009 11:42 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
That update statement shouldn't be there either.
Just do a direct assignment to :new.latefee
Re: Trigger Help [message #431838 is a reply to message #431824] Thu, 19 November 2009 12:03 Go to previous messageGo to next message
blammo04
Messages: 5
Registered: November 2009
Junior Member
CREATE OR REPLACE TRIGGER latefree
BEFORE INSERT OR UPDATE ON charters
FOR EACH ROW
DECLARE
late number(8,2);
BEGIN
IF(:new.actualreturn) >(:new.plannedreturn) THEN
late = ((:new.actualreturn)-(:new.plannedreturn))*100;
--RAISE_Application_Error(-20001, 'LATE FEE REQUIRED');
END IF;
END;

/




okay I have got the IF to work correct but its giving me an error with the

late = ((:new.actualreturn)-(:new.plannedreturn))*100;


is there a better way to go about doing this?
Re: Trigger Help [message #431839 is a reply to message #431824] Thu, 19 November 2009 12:04 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Telling us what the error is would be a good start.
Re: Trigger Help [message #431841 is a reply to message #431824] Thu, 19 November 2009 12:06 Go to previous messageGo to next message
blammo04
Messages: 5
Registered: November 2009
Junior Member
Warning: Trigger created with compilation errors.
Re: Trigger Help [message #431846 is a reply to message #431824] Thu, 19 November 2009 12:09 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
type show errors in sqlplus to display the actual error.
Re: Trigger Help [message #431847 is a reply to message #431838] Thu, 19 November 2009 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 19 November 2009 18:40

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Use SQL*Plus and copy and paste your session.

[Updated on: Thu, 19 November 2009 12:10]

Report message to a moderator

Re: Trigger Help [message #431849 is a reply to message #431824] Thu, 19 November 2009 12:13 Go to previous messageGo to next message
blammo04
Messages: 5
Registered: November 2009
Junior Member
SQL> CREATE OR REPLACE TRIGGER latefree
2 BEFORE INSERT OR UPDATE ON charters
3 FOR EACH ROW
4 DECLARE
5 late number(8,2);
6 BEGIN
7 IF(:new.actualreturn) >(:new.plannedreturn) THEN
8 late = ((:new.actualreturn)-(:new.plannedreturn))*100;
9
10 --RAISE_Application_Error(-20001, 'LATE FEE REQUIRED');
11
12 END IF;
13 END;
14
15 /

Warning: Trigger created with compilation errors.

SQL> show errors latefree;
Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
PACKAGE BODY | TRIGGER | VIEW
| TYPE | TYPE BODY | DIMENSION
| JAVA SOURCE | JAVA CLASS } [schema.]name]
SQL>
Re: Trigger Help [message #431850 is a reply to message #431824] Thu, 19 November 2009 12:15 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oracle tells you what syntax to use right there!

just:
show errors

would have done, otherwise:
show errors trigger latefree


And shouldn't the trigger be called latefee?
Re: Trigger Help [message #431861 is a reply to message #431849] Thu, 19 November 2009 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 19 November 2009 19:09
Michel Cadot wrote on Thu, 19 November 2009 18:40

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel


Use SQL*Plus and copy and paste your session.


Re: Trigger Help [message #431872 is a reply to message #431824] Thu, 19 November 2009 13:30 Go to previous messageGo to next message
blammo04
Messages: 5
Registered: November 2009
Junior Member
CREATE OR REPLACE TRIGGER latefree
BEFORE INSERT OR UPDATE ON charters
FOR EACH ROW
DECLARE
late number(8);
BEGIN
IF(:new.actualreturn) >(:new.plannedreturn) THEN
late := (:new.actualreturn-:new.plannedreturn *100;
END IF;
END;
/

Trigger Created.


How would I take late and UPDATE It in the charters table and make the column latefee = late????

I tried this but it doesnt work.

BEGIN
IF(:new.actualreturn) >(:new.plannedreturn) THEN
late := (:new.actualreturn-:new.plannedreturn*100;
UPDATE charters
SET lateFee = late;
END IF;
END;




anybody have any suggestions?
Re: Trigger Help [message #431873 is a reply to message #431872] Thu, 19 November 2009 13:35 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I suggest you re-read the thread, because the problem was already mentioned by cookiemonster.

Also READ AND FOLLOW the forum guide already mentioned by Michel three times.

[Updated on: Thu, 19 November 2009 13:37]

Report message to a moderator

Re: Trigger Help [message #431878 is a reply to message #431872] Thu, 19 November 2009 14:08 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
Why even use the intermediary variable called "late?" Just put it directly into the column with the proper way to reference columns (you have already done this with other columns in this same trigger).
Re: Trigger Help [message #431910 is a reply to message #431872] Fri, 20 November 2009 00:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
blammo04 wrote on Thu, 19 November 2009 20:30
CREATE OR REPLACE TRIGGER latefree
BEFORE INSERT OR UPDATE ON charters
FOR EACH ROW
DECLARE
late number(8);
BEGIN
IF(:new.actualreturn) >(:new.plannedreturn) THEN
late := (:new.actualreturn-:new.plannedreturn *100;
END IF;
END;
/

Trigger Created.

You are cheating.
This trigger is not valid. It misses a closing parenthesis.
You should get rid of the parentheses anyway, they don't add anything.
Re: Trigger Help [message #432062 is a reply to message #431824] Fri, 20 November 2009 14:32 Go to previous message
Bill B
Messages: 1457
Registered: December 2004
Senior Member
almost there
CREATE OR REPLACE TRIGGER latefree
BEFORE INSERT OR UPDATE ON charters
FOR EACH ROW
BEGIN
IF(:new.actualreturn) >(:new.plannedreturn) THEN
  :new.latefee := (:new.actualreturn-:new.plannedreturn) *100;
else
 :new.latefee := 0;
END IF;
END;
/

[Updated on: Fri, 20 November 2009 14:33]

Report message to a moderator

Previous Topic: Selecting the maximum value from the Group (merged 3)
Next Topic: SELECT statement with IF/ELSe or CASE
Goto Forum:
  


Current Time: Tue Sep 27 14:31:36 CDT 2016

Total time taken to generate the page: 0.11450 seconds