Home » SQL & PL/SQL » SQL & PL/SQL » TRIGGER For Updating row if exists and insert new row if it not exists (Oracle 10g ,XP)
icon9.gif  TRIGGER For Updating row if exists and insert new row if it not exists [message #311287] Thu, 03 April 2008 23:23 Go to next message
suneelvarma_27
Messages: 28
Registered: April 2008
Junior Member
hi all
CREATE OR REPLACE TRIGGER emp_t
 instead of INSERT ON empv
 REFERENCING NEW AS n
 FOR EACH ROW
 DECLARE
    rowcnt number;
 BEGIN
    SELECT COUNT(*) INTO rowcnt FROM empv WHERE id = :n.id;
    IF rowcnt = 0  THEN
        INSERT INTO empv VALUES (:n.id, :n.name,:n.sal);
    ELSE
       UPDATE empv SET sal = sal+:n.sal
          WHERE id = :n.id;
    END IF;
 END;


this code is working in oracle 9i but shows errors in oracle 10g
could you pls tell me what i have to do
errors: ORA-06512 and ORA-04088 :blush:
Re: TRIGGER For Updating row if exists and insert new row if it not exists [message #311288 is a reply to message #311287] Thu, 03 April 2008 23:29 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
04088, 00000, "error during execution of trigger '%s.%s'"
// *Cause:  A runtime error occurred during execution of a trigger.
// *Action: Check the triggers which were involved in the operation.


Please learn to use GOOGLE to lookup error codes!
Re: TRIGGER For Updating row if exists and insert new row if it not exists [message #311301 is a reply to message #311287] Fri, 04 April 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will not work properly in multi-user environment.
MERGE is the statement you have to use.

Regards
Michel
Re: TRIGGER For Updating row if exists and insert new row if it not exists [message #311819 is a reply to message #311301] Mon, 07 April 2008 00:23 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

suneelvarma_27 Wrote On Thu, 03 April 2008 23:23

this code is working in oracle 9i but shows errors in oracle 10g



Are you not getting any Mutating table error in this code??



Previous Topic: Rounding Decimal Numbers
Next Topic: conditions for analytic function
Goto Forum:
  


Current Time: Wed Dec 07 20:03:19 CST 2016

Total time taken to generate the page: 0.09001 seconds