mardi 18 octobre 2016

triggers including if else statements

ive got most of this trigger working, i just cant see how to properly implement this if else, in which i try evaluate the long and latitudes into there respective north, east.. etc.

CREATE OR REPLACE TRIGGER TR_SIGHTING_DESC
BEFORE INSERT ON sightings
FOR EACH ROW
DECLARE bn birds.bird_name%type;
DECLARE NS VARCHAR2(10), EW VARCHAR2(10);
BEGIN
IF :new.latitude >= 0 THEN
    EW := 'East'
ELSE
    EW := 'West'
END IF;

IF :new.longitude >= 0 THEN
    NS := 'North'
ELSE
    NS := 'South'
END IF;
SELECT bird_name
INTO bn
from birds
where bird_id = :new.bird_id;    
    :new.description := 'A bird of the species ' 
                        || bn 
                        || ' was spotted in the ' 
                        || EW|| '-'|| NS
                        || ' part of the observation area';
END;
/

INSERT INTO sightings (spotter_id, bird_id, latitude,
longitude, sighting_date)
VALUES (1024, 512, -25.6, 153, '09-MAR-2016');

very new to sql for if then statements, and triggers in general. any help would be great!

Aucun commentaire:

Enregistrer un commentaire