Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not working (Oracle 9i Win XP)
Trigger not working [message #317077] Tue, 29 April 2008 07:51 Go to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
Trigger not working. Pls somebody help me.
My Trigger is :
CREATE OR REPLACE TRIGGER FIVE_PER
AFTER INSERT ON SALES
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE SALES SET DISC=5 WHERE ITEM_ID = :NEW.ITEM_ID;
END IF;
END;
/
Re: Trigger not working [message #317082 is a reply to message #317077] Tue, 29 April 2008 08:05 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Yet another chrystal ball problem Smile

How do you know that your trigger is not working?

Let me guess: you get an error while trying to insert a record?

Maybe it would be easier for all of us if you posted the error message alongside the insert-statement you tried.

(or: you provide us with the proper privileges to log on onto your systems so we can snoop around and help you Razz )
Re: Trigger not working [message #317083 is a reply to message #317077] Tue, 29 April 2008 08:05 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Have a look at Mutating trigger.
Re: Trigger not working [message #317094 is a reply to message #317077] Tue, 29 April 2008 08:39 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
I don`t know what you exactly want to do, but if what you need is setting a default value on your column, why not using a default value on the table column or using a BEFORE ROW trigger instead. This way you don`t have to code an update statement

CREATE OR REPLACE TRIGGER FIVE_PER
BEFORE INSERT ON SALES
FOR EACH ROW
BEGIN
  :new.disc := 5;
END;

Re: Trigger not working [message #317096 is a reply to message #317077] Tue, 29 April 2008 08:46 Go to previous messageGo to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
I want to insert value in sales table as :
INSERT INTO SALES(SALE_ID,QTY,ITEM_ID) VALUES(1,1,1);

and the error is :
ERROR at line 1:
ORA-04091: table RETAIL.SALES is mutating, trigger/function may not see it
ORA-06512: at "RETAIL.FIVE_PER", line 3
ORA-04088: error during execution of trigger 'RETAIL.FIVE_PER'

[Updated on: Tue, 29 April 2008 08:48]

Report message to a moderator

Re: Trigger not working [message #317098 is a reply to message #317096] Tue, 29 April 2008 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joy_division gave you the error you will have and what you have to search to fix it.
Kaeluan gave you the solution for something you didn't explain.
I think this forum is quite good! Smile

Regards
Michel

Re: Trigger not working [message #317102 is a reply to message #317098] Tue, 29 April 2008 09:27 Go to previous messageGo to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
I have solved the problem. Thanks to all the member of the forum. Cool
Re: Trigger not working [message #317108 is a reply to message #317102] Tue, 29 April 2008 09:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
And how about sharing the solution with us and updating the title with more meaningful message rather than simply saying "Trigger not working".

Regards

Raj
Re: Switching between Triggers [message #317109 is a reply to message #317108] Tue, 29 April 2008 10:11 Go to previous messageGo to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
How to switch between triggers ?
Re: Switching between Triggers [message #317111 is a reply to message #317109] Tue, 29 April 2008 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to switch between triggers ?

What do you mean?

Regards
Michel
Re: Switching between Triggers [message #317113 is a reply to message #317111] Tue, 29 April 2008 10:22 Go to previous messageGo to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
Switching means Enabling or Disabling the trigger.
Re: Switching between Triggers [message #317115 is a reply to message #317113] Tue, 29 April 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALTER TRIGGER

Regards
Michel
Re: Switching between Triggers [message #317118 is a reply to message #317115] Tue, 29 April 2008 10:38 Go to previous message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
Thanks a lot Michel! Smile
Previous Topic: csv to table
Next Topic: Passing variable in from select statement to host command?
Goto Forum:
  


Current Time: Thu Dec 08 00:42:20 CST 2016

Total time taken to generate the page: 0.06393 seconds