Home » SQL & PL/SQL » SQL & PL/SQL » about trigger
about trigger [message #200535] Tue, 31 October 2006 05:10 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
hi everyone some of the erro, but i dont know whats wrong with this

the code is to create a invoice_paid which will fire when an existing recird is updated in the invoice table. if the status value in the updated row is 'paid' then the corresponding campaign record must be updated(datepaid is assigned syssdate); if the status value in the updated row is 'overdue', then a suitable message is printed oni the screen as a reminder to send an overdue notice..

the following is my code...

--Createing Trigger for invoice_paid--
CREATE OR REPLACE TRIGGER invoice_paid
AFTER UPDATE OF status ON invoice
FOR EACH ROW
BEGIN
  IF(:NEW.status = 'Paid')
  UPDATE campaign
    SET datepaid = SYSDATE
    WHERE title = new.title;
  ELSEIF NEW.status = 'overdue';
  DBMS_output.put_line('the payment is overdue!');
  ENDIF
END;
/


the error message is follow;;;;

Warning: Trigger created with compilation errors.
Errors for TRIGGER INVOICE_PAID:

LINE/COL ERROR
3/3 PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following: * & = - + < / > at in is mod remainder not rem then <an exponent (**)> <> or != or ~= >= <= <> and or like L IKE2_ LIKE4_ LIKEC_ between overlaps || multiset year DAY_ mem ber SUBMULTISET_ The symbol "then" was substituted for "UPDATE " to continue.
5/22 PLS-00103: Encountered the symbol "." when expecting one of the f ollowing: mod <an identifier> <a double-quoted delimited-iden tifier> <a bind variable> current sql execute forall merge pip e The symbol "<an identifier>" was substituted for "." to continu e.
6/10 PLS-00103: Encountered the symbol "NEW" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "NEW" to continue.
9/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( @ % ;

[Updated on: Tue, 31 October 2006 05:11]

Report message to a moderator

Re: about trigger [message #200537 is a reply to message #200535] Tue, 31 October 2006 05:17 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

some syntax errors.make this changes and see if works.
also prefix colon (:) if you are refering NEW or OLD values.(eg. :new.col_name).

CREATE OR REPLACE TRIGGER invoice_paid
AFTER UPDATE OF status ON invoice
FOR EACH ROW
BEGIN
  if (condition) then
<statement>
elsif
<statement>
end if;
END;


regards,

[Updated on: Tue, 31 October 2006 05:24]

Report message to a moderator

Re: about trigger [message #200541 is a reply to message #200537] Tue, 31 October 2006 05:39 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
is this what you mean



set serveroutput on;
CREATE OR REPLACE TRIGGER invoice_paid
AFTER UPDATE OF status ON invoice
FOR EACH ROW
BEGIN
	IF(:NEW.status := 'Paid') THEN
		UPDATE campaign
		SET datepaid = SYSDATE
		WHERE title := new.title;
	ELSIF (NEW.status := 'overdue')
		DBMS_output.put_line('the payment is overdue!');
	END IF;
END;
/

Re: about trigger [message #200545 is a reply to message #200541] Tue, 31 October 2006 05:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, it wasn't.

Convert the ':=' in your IF statements to '=', and (as the previous poster said:

Quote:
also prefix colon (Smile if you are refering NEW or OLD values.(eg. :new.col_name)
Re: about trigger [message #200547 is a reply to message #200535] Tue, 31 October 2006 05:48 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member

set serveroutput on;
CREATE OR REPLACE TRIGGER invoice_paid
AFTER UPDATE OF status ON invoice
FOR EACH ROW
BEGIN
	IF(:NEW.status = 'Paid') THEN
		UPDATE campaign
		SET datepaid = SYSDATE
		WHERE title = :new.title;
	ELSIF (:NEW.status = 'overdue')
		DBMS_output.put_line('the payment is overdue!');
	END IF;
END;
/
Re: about trigger [message #200549 is a reply to message #200547] Tue, 31 October 2006 05:52 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

just a small change in your code

set serveroutput on
CREATE OR REPLACE TRIGGER invoice_paid
AFTER UPDATE OF status ON invoice
FOR EACH ROW
BEGIN
	IF(:NEW.status = 'Paid') THEN
		UPDATE campaign
		SET datepaid = SYSDATE
		WHERE title = :new.title;
	Elsif (:new.status ='Overdue') then

		DBMS_output.put_line('the payment is overdue!');
	END IF;
END;


regards,

[Updated on: Tue, 31 October 2006 05:54]

Report message to a moderator

Re: about trigger [message #200552 is a reply to message #200535] Tue, 31 October 2006 06:00 Go to previous messageGo to next message
white
Messages: 13
Registered: October 2006
Junior Member


Hi,
whatever errors u got just u make small chages in your code.

1.put then for IF statement at 5th line.
2.put colon(Smile before new.title.
3.in 9th line that put else rather elseif
and put colon(Smile before new.status
4. 11th line give space between end if and put semicolon(Wink
Re: about trigger [message #200553 is a reply to message #200535] Tue, 31 October 2006 06:01 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
but there is still an error

LINE/COL ERROR
5/17 PLS-00049: bad bind variable 'NEW.TITLE'
Re: about trigger [message #200554 is a reply to message #200552] Tue, 31 October 2006 06:10 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
white wrote on Tue, 31 October 2006 06:00


Hi,
whatever errors u got just u make small chages in your code.

1.put then for IF statement at 5th line.
2.put colon(Smile before new.title.
3.in 9th line that put else rather elseif
and put colon(Smile before new.status
4. 11th line give space between end if and put semicolon(Wink



do not understand the 4th one ...

well this is my code

set serveroutput on
CREATE OR REPLACE TRIGGER invoice_paid
AFTER UPDATE OF status ON invoice
FOR EACH ROW
BEGIN
	IF(:NEW.status = 'Paid') THEN
		UPDATE campaign
		SET datepaid = SYSDATE
		WHERE title = :NEW.title;
	ELSE (:NEW.status = 'overdue') THEN
		DBMS_output.put_line('the payment is overdue!');
	END IF;
END;
/

Re: about trigger [message #200555 is a reply to message #200553] Tue, 31 October 2006 06:12 Go to previous messageGo to next message
white
Messages: 13
Registered: October 2006
Junior Member

just put :new.title
u will not get that error
Re: about trigger [message #200556 is a reply to message #200554] Tue, 31 October 2006 06:15 Go to previous messageGo to next message
white
Messages: 13
Registered: October 2006
Junior Member


put elsif rather else at 9th line
ok
Re: about trigger [message #200560 is a reply to message #200556] Tue, 31 October 2006 06:27 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hiip, did you finally fix the trigger or not? You've posted several trigger codes but without explanation - does it work or not? If not, did you get any new error messages?

BTW, you do know that using DBMS_OUTPUT will work only if your end user runs this in SQL*Plus (or similar tool)? If the trigger fires because someone does something in a form, he will not see this message - you'd have to use RAISE_APPLICATION_ERROR instead.
Re: about trigger [message #200565 is a reply to message #200535] Tue, 31 October 2006 06:39 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
wat the code will do:

the code is to create a invoice_paid which will fire when an existing recird is updated in the invoice table. if the status value in the updated row is 'paid' then the corresponding campaign record must be updated(datepaid is assigned syssdate); if the status value in the updated row is 'overdue', then a suitable message is printed oni the screen as a reminder to send an overdue notice..

this is the code tat i got now
--------------------Createing Trigger for invoice_paid--------------------
set serveroutput on
CREATE OR REPLACE TRIGGER invoice_paid
AFTER UPDATE OF status ON invoice
FOR EACH ROW
BEGIN
	IF(:NEW.status = 'Paid') THEN
		UPDATE campaign
		SET datepaid = SYSDATE
		WHERE title = :NEW.title;
	ELSIF (:NEW.status = 'overdue') THEN
		DBMS_output.put_line('the payment is overdue!');
	END IF;
END;
/


Re: about trigger [message #200566 is a reply to message #200565] Tue, 31 October 2006 06:42 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, it is the same code (for the fourth time) that we've already seen. But does it work? Did you try it? Yes? And?

[Updated on: Tue, 31 October 2006 06:42]

Report message to a moderator

Re: about trigger [message #200568 is a reply to message #200535] Tue, 31 October 2006 06:46 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
it is not working as the error code is

Warning: Trigger created with compilation errors.
Errors for TRIGGER INVOICE_PAID:

LINE/COL ERROR
5/17 PLS-00049: bad bind variable 'NEW.TITLE'
Re: about trigger [message #200570 is a reply to message #200568] Tue, 31 October 2006 06:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
From the error, it looks like your INVOICE table doesn't have a column called TITLE in it.
Re: about trigger [message #200572 is a reply to message #200535] Tue, 31 October 2006 06:54 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
oh yeah... i rename it to campaigntitle as it cannot have duplicate name similar in another table.. damn.... should be ever to find that error quickly but .. anyway.. thank you so so much for the help ... and really appreciate it ... thnx Smile
Re: about trigger [message #200575 is a reply to message #200535] Tue, 31 October 2006 07:00 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
there is another error in my body of the package..
the following code is


--------------------Creating a package of assignment3--------------------
CREATE OR REPLACE PACKAGE assignment3 
IS

PROCEDURE list_campaigns(cname IN campaign.companyname%type);

PROCEDURE end_campaign(ctitle IN campaign.title%type);

PROCEDURE delete_campaign(ctitle IN campaign.title%type);

FUNCTION top_client RETURN client.companyname%type;

END ASSIGNMENT3;
/
--------------------end of Creating a package of assignment3--------------------

CREATE OR REPLACE PACKAGE BODY assignment3 AS
--------------------procedure list_campaigns--------------------
PROCEDURE list_campaigns
	(cname IN campaign.title%TYPE) 
IS
	CURSOR findCampaign IS
		SELECT title, estimatedcost, actualcost
        FROM campaign
        WHERE title = cname;
BEGIN
    FOR findCampaign_rec IN findCampaign LOOP
		DBMS_OUTPUT.PUT_LINE(findCampaign_rec);
	END LOOP;
END list_campaigns;
/
END assignment3


this sis the error message

please!!!

Package created.

Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY ASSIGNMENT3:

LINE/COL ERROR
14/19 PLS-00103: Encountered the symbol "end-of-file" when expecting on e of the following: begin end function package pragma procedu re form
Re: about trigger [message #200576 is a reply to message #200575] Tue, 31 October 2006 07:03 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I'm not wrong, instead of
END list_campaigns;
/
END assignment3

there should be
END list_campaigns;
END assignment3
/
Re: about trigger [message #200580 is a reply to message #200535] Tue, 31 October 2006 07:16 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
while,,, when i insert this


CREATE OR REPLACE PACKAGE BODY assignment3 AS
--------------------procedure list_campaigns--------------------
PROCEDURE list_campaigns
	(cname IN campaign.title%TYPE) 
IS
	CURSOR findCampaign IS
		SELECT title, estimatedcost, actualcost
        FROM campaign
        WHERE title = cname;
BEGIN
    FOR findCampaign_rec IN findCampaign LOOP
		DBMS_OUTPUT.PUT_LINE(findCampaign_rec);
	END LOOP;
END list_campaigns;
/



to compile it,,
it gives me same error ...
Re: about trigger [message #200581 is a reply to message #200580] Tue, 31 October 2006 07:23 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This code passes correctly through Formatter Plus:
/* Formatted on 2006/10/31 14:21 (Formatter Plus v4.8.0) */
--------------------Creating a package of assignment3--------------------

CREATE OR REPLACE PACKAGE assignment3
IS
   PROCEDURE list_campaigns (cname IN campaign.companyname%TYPE);

   PROCEDURE end_campaign (ctitle IN campaign.title%TYPE);

   PROCEDURE delete_campaign (ctitle IN campaign.title%TYPE);

   FUNCTION top_client
      RETURN client.companyname%TYPE;
END assignment3;
/

--------------------end of Creating a package of assignment3--------------------

CREATE OR REPLACE PACKAGE BODY assignment3
AS
--------------------procedure list_campaigns--------------------
   PROCEDURE list_campaigns (cname IN campaign.title%TYPE)
   IS
      CURSOR findcampaign
      IS
         SELECT title, estimatedcost, actualcost
           FROM campaign
          WHERE title = cname;
   BEGIN
      FOR findcampaign_rec IN findcampaign
      LOOP
         DBMS_OUTPUT.put_line (findcampaign_rec);
      END LOOP;
   END list_campaigns;
END assignment3;
/
In my previous post, I *forgot* (right, didn't see it ...) to put a semi-colon behind "END assignment3".
Re: about trigger [message #200582 is a reply to message #200535] Tue, 31 October 2006 07:33 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
the error come up again

/11 	PLS-00323: subprogram or cursor 'LIST_CAMPAIGNS' is declared in a package specification and must be defined in the package body
12/3 	PL/SQL: Statement ignored
12/3 	PLS-00306: wrong number or types of arguments in call to 'PUT_LIN E'

Re: about trigger [message #200584 is a reply to message #200582] Tue, 31 October 2006 07:38 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's a difference between those two:

Package specification:

PROCEDURE list_campaigns (cname IN campaign.companyname%TYPE);

Package body:

PROCEDURE list_campaigns (cname IN campaign.title%TYPE)

------------------

Wrong number or type of arguments: you can not output the whole cursor variable, but its elements, for example

DBMS_OUTPUT.PUT_LINE(findcampaign_rec.title ||' '||findcampaign_rec.estimatedcost)
Re: about trigger [message #200588 is a reply to message #200584] Tue, 31 October 2006 07:41 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
do you mean this

CREATE OR REPLACE PROCEDURE list_campaign
	(cname IN campaign.companyname%Type)
IS
	CURSOR list_campaign_cur IS
		SELECT title, estimatedcost, actualcost, companyname
		FROM campaign;
BEGIN
	FOR list_campaign_rec IN list_campaign_cur LOOP
		IF cname = list_campaign_rec.companyname THEN
			DBMS_OUTPUT.PUT_LINE( list_campaign_cur.TITLE || ' ' || 'Estimated Cost: ' || 
			list_campaign_cur.estimatedcost || ' ' ||' Actual Cost: ' || list_campaign_cur.actualcost );
		END IF;
	END LOOP;
END;
/



error comes out

0/4 PL/SQL: Statement ignored
10/44 PLS-00225: subprogram or cursor 'LIST_CAMPAIGN_CUR' reference is out of scope

[Updated on: Tue, 31 October 2006 07:46]

Report message to a moderator

Re: about trigger [message #200589 is a reply to message #200588] Tue, 31 October 2006 07:43 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm ... may be. Did you try it?
Re: about trigger [message #200594 is a reply to message #200535] Tue, 31 October 2006 07:52 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
yes i did , this is the error message come out after compile

error comes out

0/4 PL/SQL: Statement ignored
10/44 PLS-00225: subprogram or cursor 'LIST_CAMPAIGN_CUR' reference is out of scope
Re: about trigger [message #200597 is a reply to message #200594] Tue, 31 October 2006 07:57 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Uh oh ... of course. Not _CUR, but _REC!

DBMS_OUTPUT.PUT_LINE(something_REC.title)

not

DBMS_OUTPUT.PUT_LINE(something_CUR.title)

I give up. Going home. Food. Sleep.

This is out of control, I'm afraid ./fa/917/0/

If you can't figure that out, and if noone can help you at the moment, I'll try to *carefully* read and properly answer your question. Sorry.
Re: about trigger [message #200599 is a reply to message #200594] Tue, 31 October 2006 08:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
hiip wrote on Tue, 31 October 2006 08:52
yes i did , this is the error message come out after compile

error comes out

0/4 PL/SQL: Statement ignored
10/44 PLS-00225: subprogram or cursor 'LIST_CAMPAIGN_CUR' reference is out of scope


You need to use LIST_CAMPAIGN_REC, not LIST_CAMPAIGN_CUR in your references.
Re: about trigger [message #200601 is a reply to message #200599] Tue, 31 October 2006 08:08 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
i want to ask that if i use
DBMS_OUTPUT.PUT_LINE(findCampaign_rec);
instead of this
DBMS_OUTPUT.PUT_LINE( list_campaign_rec.title || ' ' || 'Estimated Cost: ' ||
list_campaign_rec.estimatedcost || ' ' ||' Actual Cost: ' || list_campaign_rec.actualcost );

wat will be the output and what is the diff??
Re: about trigger [message #200603 is a reply to message #200601] Tue, 31 October 2006 08:11 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
hiip wrote on Tue, 31 October 2006 09:08
i want to ask that if i use
DBMS_OUTPUT.PUT_LINE(findCampaign_rec);
instead of this
DBMS_OUTPUT.PUT_LINE( list_campaign_rec.title || ' ' || 'Estimated Cost: ' ||
list_campaign_rec.estimatedcost || ' ' ||' Actual Cost: ' || list_campaign_rec.actualcost );

wat will be the output and what is the diff??


Well, why don't YOU try it and tell us what the difference is.
Re: about trigger [message #200609 is a reply to message #200603] Tue, 31 October 2006 08:37 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
the error shows up

Warning: Procedure created with compilation errors.
Errors for PROCEDURE LIST_CAMPAIGNS:

LINE/COL ERROR
10/3 PL/SQL: Statement ignored
10/3 PLS-00306: wrong number or types of arguments in call to 'PUT_LIN E'

while i try this


PROCEDURE list_campaigns
	(cname IN campaign.title%TYPE) 
IS
	CURSOR findCampaign IS
		SELECT title, estimatedcost, actualcost
        FROM campaign
        WHERE title = cname;
BEGIN
    FOR findCampaign_rec IN findCampaign LOOP
		DBMS_OUTPUT.PUT_LINE(findCampaign_rec);
	END LOOP;
END list_campaigns;
/

Re: about trigger [message #200651 is a reply to message #200609] Tue, 31 October 2006 13:46 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hiip, look, you (we) are turning the same sentence and code upside down, from left to right and nothing seems to help. Did you ever try to read previous posts, explanations, suggestions, or (God forbid!) read documentation regarding packages, procedures and cursors? If you did, I bet it was a very thin book.

Therefore, here is a working example. Read and re-read it until you finally figure out who, why and how it is done.

First, create a package and package body:
SQL> CREATE OR REPLACE PACKAGE pkg_hiip
  2  AS
  3     PROCEDURE prc_list (par_deptno IN DEPT.deptno%TYPE);
  4  END pkg_hiip;
  5  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pkg_hiip
  2  AS
  3     PROCEDURE prc_list (par_deptno IN DEPT.deptno%TYPE)
  4     IS
  5     BEGIN
  6        FOR emp_rec IN (SELECT ename, job
  7                          FROM EMP
  8                         WHERE deptno = par_deptno)
  9        LOOP
 10           DBMS_OUTPUT.put_line (emp_rec.ename || ' ' || emp_rec.job);
 11        END LOOP;
 12     END prc_list;
 13  END pkg_hiip;
 14  /

Package body created.

SQL>

Now execute this procedure with a parameter - it is necessary to specify package name AND a procedure name:
SQL> execute pkg_hiip.prc_list(10);
CLARK MANAGER
KING PRESIDENT
MILLER CLERK

PL/SQL procedure successfully completed.

SQL>

I hope you'll be able to figure it out. Not today, it is getting late here, perhaps not tomorrow, but please, read it carefully and try not to repeat the same mistakes (and get the same error messages) all over again.
Previous Topic: Drop Down List in MOD Pl/sql
Next Topic: How do you read and print a BLOB field when doing a file upload
Goto Forum:
  


Current Time: Tue Dec 06 08:28:33 CST 2016

Total time taken to generate the page: 0.17389 seconds