CODEWITHSUNDEEP
sqloracle-databasetriggers
Thangnv
Thangnv

Reputation: 815

Error with SQL create Trigger oracle

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!

Upvotes: 0

Views: 955

Answers (2)

Dba
Dba

Reputation: 6639

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;

Upvotes: 2

asantaballa
asantaballa

Reputation: 4038

I think because you have no data type for lat

DECLARE lat, lng sell_point.sellpoint_lat%TYPE;
           ^

Upvotes: 0

Related Questions