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?