Need help with a trigger

[ferretwoman]

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

Thanks!

Linda

[dickersonka]

you need to correct the insert statement portion

insert into parentchild(child, parent, parentchild)
values(@child, @parentid, @parent + " / " + @child)

[ferretwoman]

Thanks for the help so far.

I've gotten this far with it now:

CREATE TRIGGER parent_child ON parentchild
after insert

AS

DECLARE @parent as nvarchar(20)
DECLARE @child as nvarchar(20)
DECLARE @parentid as numeric
DECLARE @childid as numeric
DECLARE @parentchild as nvarchar(40)


BEGIN

select @parentid = i.parent, @childid = i.child
@parent = p.parent, @child=i.child, 
from inserted i, parent p
where p.parentid = i.parent
 @parentchild = (@parent + " / " + @child)
  

insert into parentchild(child, parent, parentchild)
values(@childid, @parentid, @parentchild)


END
GO

I'm still getting a syntax error:
Incorrect syntax near '@parent'.
which I guess is referring to my @parentchild = (@parent + "/" + @child) line. Can anyone point out the problem with that for me?

Thanks!

Linda

[dickersonka]

try adding the word AND in your where statement

[ferretwoman]

Thanks! Finally figured it out - a few things missing, a few things changed. Final code that compiles (haven't tested to make sure it runs properly yet though) in case it would help others.

CREATE TRIGGER parent_child ON parentchild
after insert

AS

DECLARE @parent as nvarchar(20)
DECLARE @child as nvarchar(20)
DECLARE @parentid as numeric
DECLARE @childid as numeric
DECLARE @parentchild as nvarchar(40)


BEGIN

select @parentid = i.parent, @childid = i.child,
@parent = p.name, @child=i.child 
from inserted i, parent p
where p.parentid = i.parent

set @parentchild = (@parent + ' / ' + @child)
  

insert into parentchild(child, parent, parentchild)
values(@childid, @parentid, @parentchild)


END
GO

Thanks!

Linda

[dickersonka]

A suggestion would be not to use numeric unless you needed to, i'm assuming parentid and childid could be int

also nvarchar is used for multilingual data, if you have that keep it, otherwise use varchar