Re: Creating a trigger to search in another table...

[Rick Langley]

Found a couple of errors...

Needed some semi-colons.
Neeeded to change NUMBER with DECIMAL.
A couple of text changes.
Added declaration of variable "i".

But I'm still stuck!

Please help me once again. :)

Now I'm getting an error saying "Unknown column 'V1' in 'NEW'" any ideas?

------------------------

DELIMITER $$
CREATE TRIGGER test.trg_DATATABLE
BEFORE INSERT ON test.DATATABLE
FOR EACH ROW
BEGIN

DECLARE V1 decimal(10,2);
DECLARE V2 decimal(10,2);
DECLARE i char(20);

SET V1 = NEW.latvalue/100;
SET V2 = NEW.longvalue/100;

IF NEW.NorthOrSouth = 'S' THEN
SET V1 = V1 * -1;
END IF;

IF New.EastOrWest = 'W' THEN
SET V2 = V2 * -1;
END IF;

SELECT Title
INTO i
FROM test.GRIDTABLE
WHERE latlow <= NEW.V1
AND lathigh > NEW.V1
AND longlow <= NEW.V2
AND longhigh > NEW.V2;

SET NEW.Title = i;
END;
$$

Q. What is big and has no (inter)-face?

A. My database.