MS SQL

MS SQL RSS

Need help with a trigger

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jun 2008

Posts: 3

Reputation: ferretwoman is an unknown quantity at this point

Solved Threads: 0

ferretwoman ferretwoman is offline

Offline

Newbie Poster

Need help with a trigger

Oct 27th, 2008

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Need help with a trigger

Oct 27th, 2008

you need to correct the insert statement portion

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
INSERT INTO parentchild(child, parent, parentchild)
VALUES(@child, @parentid, @parent + " / " + @child)
insert into parentchild(child, parent, parentchild)
values(@child, @parentid, @parent + " / " + @child)

Custom Application & Software Development
www.houseshark.net

•

Join Date: Jun 2008

Posts: 3

Reputation: ferretwoman is an unknown quantity at this point

Solved Threads: 0

ferretwoman ferretwoman is offline

Offline

Newbie Poster

Re: Need help with a trigger

Oct 27th, 2008

Thanks for the help so far.

I've gotten this far with it now:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Need help with a trigger

Oct 27th, 2008

try adding the word AND in your where statement

Custom Application & Software Development
www.houseshark.net

•

Join Date: Jun 2008

Posts: 3

Reputation: ferretwoman is an unknown quantity at this point

Solved Threads: 0

ferretwoman ferretwoman is offline

Offline

Newbie Poster

Re: Need help with a trigger

Oct 27th, 2008

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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Need help with a trigger

Oct 27th, 2008

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

Last edited by dickersonka; Oct 27th, 2008 at 4:37 pm.

Custom Application & Software Development
www.houseshark.net