CODEWITHSUNDEEP
mysqldatabasetriggersdatabase-trigger
marianod323
marianod323

Reputation: 37

Can I have a trigger that uses different tables argumments?

I'm making a simple application with MySql and I need to use a trigger. The trigger that I thought would update the vehicle table everytime a person is updated with a specific civil state.

I'm not sure about the trigger syntax, but I didn't find anything that explains the use of multiple argumments from differents tables.

Here's my trigger:

CREATE TRIGGER tr_widow AFTER UPDATE
ON person
FOR EACH ROW
SET v.id_person = p.id_spouse 
FROM person p 
JOIN person p2 ON p.id_spouse = p2.id_person 
JOIN vehicle v ON v.id_person = p.id_person 
WHERE p2.civil_state = 5

It's returning "Unknown system variable "id_person"" error.

Upvotes: 1

Views: 31

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

We use UPDATE statement (not SET clause) to update values in table(s). I have changed the Trigger below (fixing all syntax errors). But logically, you still need to define exactly what you are trying to achieve.

In Triggers, we can access NEW and OLD value to achieve our logic.

DELIMITER $$  -- Define the Delimiter to something else other than ;
CREATE TRIGGER tr_widow AFTER UPDATE
ON person
FOR EACH ROW

  BEGIN  -- Trigger statement body starts with BEGIN clause

    UPDATE vehicle v 
    JOIN person p ON v.id_person = p.id_person 
    JOIN pessoa p2 ON p.id_spouse = p2.id_person 
    SET v.id_person = p.id_spouse 
    WHERE p2.civil_state = 5;

  END $$  -- END the Trigger statement
DELIMITER ;  -- redefine the Delimiter to ;

Upvotes: 1

Related Questions