New to triggers and SQL programming but I'm trying to figure it out by looking at code examples. I have a table, called child. My program inserts a new child into the child table and stores the parentid as a FK. What I need to do is when a new child is inserted into the table, I need to take the childid, parentid, and childname as well as the parentname , and put it in this new table called parentchild. I can get the childid, childname, and parentid from the inserted items to the child table, but I need to use the parentid to do a lookup in the parent table to get the actual string name of parent. Finally, I need to also insert in the parentchild table the name of parent/child separated by a slash.
I've attempted code below to do so. Could you tell me if I'm going in the right direction, and what I would need to do to actually get it to work as I've explained above. Here is my code:
CREATE TRIGGER parent_child ON parentchild FOR insert AS DECLARE @parent as nvarchar(20); DECLARE @child as nvarchar(20); DECLARE @parentid as int; DECLARE @childid as int; BEGIN SELECT * FROM INSERTED BEGIN SELECT @parentid AS parent from INSERTED SELECT @childid AS Child FROM INSERTED SELECT @parent as parent.name from PARENT where @parentid = parent.parentid; SELECT @child as child from INSERTED; INSERT INTO parentchild SET parentchild.child = @childid SET parentchild.parent = @parentid SET parentchild.parentchild = @parent + " / " + @child; END GO