I have a database. How do I create a trigger that uses information from two tables?

Question

I have created these two tables:

CREATE TABLE oferta
(
    id_agentie INT,
    id_spatiu INT,
    vanzare CHAR(1),
    pret INT,
    moneda CHAR(5),
    CONSTRAINT pk_oferta PRIMARY KEY(id_agentie, id_spatiu)
);

CREATE TABLE Spatiu
(
    id_spatiu INT PRIMARY KEY,
    adresa VARCHAR(45),
    zona INT,
    suprafata INT,
    id_tip INT
);

I need to create a trigger that, whenever I insert a new 'pret'.If the value of 'pret' is less than 2 * 'suprafata', then I'd like to change the value of 'pret' into 2 * 'suprafata' and the value of 'moneda' into 'EUR'.

I have tried, but I couldn't manage to get it done.

EDIT: I am using MySql. Sorry for not specifying.

Answer 1

Here is a code snippet that should anwser your need.

The trigger will run before each insert on oferta. It will first run a query to recover the value of suprafata in the corresponding record of table spatiu, then compare it to the pret value passed to the insert order. When pret is (strictly) less than twice suprafata, the trigger modifies the values of pret and moneda.

DELIMITER //
CREATE TRIGGER my_trigger
    BEFORE INSERT ON oferta
    FOR EACH ROW 
BEGIN
    DECLARE v1 INT;
    SELECT suprafata INTO v1 WHERE id_spatiu = NEW.id_spatiu;
    IF ( NEW.pret < 2 * v1) THEN
        NEW.pret = 2 * v1;
        NEW.moneda = 'EUR';
    END IF
END//
DELIMITER ;