Error with SQL create Trigger oracle

Question

I created a trigger in oracle show that:

/* Formatted on 3-Oct-2013 15:58:45 (QP5 v5.126) */
CREATE OR REPLACE TRIGGER testtrigger
AFTER INSERT OR UPDATE OF sellpoint_name
ON sell_point
FOR EACH ROW
WHEN (new.sellpoint_name = 'Location')
DECLARE lat, lng sell_point.sellpoint_lat%TYPE;
BEGIN
SELECT  sellpoint_lat, sellpoint_long into lat, lng
  FROM  sell_point
 WHERE  sellpoint_name = :new.sellpoint_name;

IF (:new.sellpoint_lat < 20 OR :new.sellpoint_long < 100)
THEN
    raise_application_error (-20225, 'this point is not exists');
END IF;
END;

but I get an error :

1/12    PLS-00103: Encountered the symbol "," when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table long double ref
char time timestamp interval date binary national character
nchar
1/47    PLS-00103: Encountered the symbol ";" when expecting one of the following:
16:10:50            := ( , not null range default external character

what wrong in here? thanks for help!

Dba · Accepted Answer · 2013-10-03 10:09:50Z

2

Try like this,

CREATE OR REPLACE TRIGGER testtrigger
AFTER INSERT OR UPDATE OF sellpoint_name ON sell_point
FOR EACH ROW
WHEN (new.sellpoint_name = 'Location')
DECLARE 
     lat sell_point.sellpoint_lat%TYPE; 
     lng sell_point.sellpoint_long%TYPE;
BEGIN
     SELECT sellpoint_lat, sellpoint_long 
       INTO lat, lng
       FROM sell_point
      WHERE sellpoint_name = :new.sellpoint_name;

     IF (:NEW.sellpoint_lat < 20 OR :NEW.sellpoint_long < 100) THEN
          raise_application_error (-20225, 'this point is not exists');
     END IF;
END;

Edited

The above code will raise the error, table is mutating, as you are trying to select the columns from the same table.

You can edit you code like this,

CREATE OR REPLACE TRIGGER testtrigger
AFTER INSERT OR UPDATE OF sellpoint_name ON sell_point
FOR EACH ROW
WHEN (new.sellpoint_name = 'Location')
BEGIN
     IF (:NEW.sellpoint_lat < 20 OR :NEW.sellpoint_long < 100) THEN
          raise_application_error (-20225, 'this point is not exists');
     END IF;
END;

edited Oct 3, 2013 at 10:09

answered Oct 3, 2013 at 9:34

Dba

6,6791 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dba Over a year ago

But why you are selecting the columns sellpoint_lat & sellpoint_long from the table, Instead you can just use :new.sellpoint_lat and :new.sellpoint_long. Else you'll get the error, table is mutating.

Thangnv Over a year ago

I want to do an action after insert sellpoint_lat < 20 or sellpoint_long < 100. Don't care "raise_application_error (-20225, 'this point is not exists');". Actualy I had an error mutating. any more explain?

Thangnv Over a year ago

I understanded your opinion. LOL.

Thangnv Over a year ago

So bad, someone voted down for my question and now I can't post any question. My god :(

asantaballa · Accepted Answer · 2013-10-03 09:18:06Z

0

I think because you have no data type for lat

DECLARE lat, lng sell_point.sellpoint_lat%TYPE;
           ^

answered Oct 3, 2013 at 9:18

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

2 Comments

Thangnv Over a year ago

I try but still error: 16:19:28 ORA-24344: success with compilation error 16:19:28 1/42 PLS-00103: Encountered the symbol "," when expecting one of the following: 16:19:28 := ( ; not null range default character

asantaballa Over a year ago

Sorry, don't see anything else :-(

Collectives™ on Stack Overflow

Error with SQL create Trigger oracle

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related