Update on a table using inner join in a trigger

Viewed 26

I have two tables in the database, they have a one-to-many relationship (Example: The table dbo.Tree [tree_id, tree_code] has many dbo.Fruits[id, name, father_tree_id, father_tree_code]) .

I'm trying to create a trigger for when the column codigo_arvore_pai of dbo.Frutos is updated or inserted into it, the column father_tree_code of dbo.Frutos is updated with the value corresponding to the tree_id of the dbo.Tree table. The condition for this is code_tree of dbo.Tree to be equal code_tree_father of dbo.Fruits.

CREATE TRIGGER [dbo].[tr_updateFruit] on [dbo].[Fruits]
AFTER INSERT, UPDATE
AS
    IF (UPDATE(father_tree_code))
    BEGIN
        UPDATE dbo.Fruits
        SET         id_arvore_pai = A.id_arvore 
        FROM        dbo.Fruits as obj
        INNER JOIN dbo.Tree A  ON  obj.father_tree_code  = A.tree_code
        WHERE obj.Id IN (SELECT DISTINCT obj.Id FROM dbo.Fruits) 
    END;

What's wrong?

0 Answers
Related