Updating Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating Table

Author

Topic

dof2001
Starting Member

4 Posts

Posted - 2010-03-03 : 12:04:58

Hello forum i am new using SQl, i need to update 4 table everytime the main table is update.
Main table get update with a new record i need to create this new record in the other forms. Basically i wan tto create aplace holder in all linked tables to main table

I was thinking on using a trigger but for some reason is not working. here is what i have, if there is a nother way please advice.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[CreateDataRecord]
   ON  [dbo].[MainForm] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO DataForm (ID, ProjectID) ;
    SELECT ID, ProjectID, FROM [dbo].[MainForm] ;

END
GO

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 12:07:56

You need to make use of the 'inserted' logical table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[CreateDataRecord]
   ON  [dbo].[MainForm] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO DataForm (ID, ProjectID)
    SELECT ID, ProjectID, FROM inserted

END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:14:23

quote:

Originally posted by vijayisonly

You need to make use of the 'inserted' logical table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[CreateDataRecord]
   ON  [dbo].[MainForm] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO DataForm (ID, ProjectID)
    SELECT ID, ProjectID, FROM inserted

END
GO

remove unwanted , at end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dof2001
Starting Member

4 Posts

Posted - 2010-03-03 : 12:19:43

Thanks fro the prompt response but as i mentioned before i am a newbie. so i dont know how to use the iserted logical table.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 12:20:46

quote:
Originally posted by dof2001

Thanks fro the prompt response but as i mentioned before i am a newbie. so i dont know how to use the iserted logical table.

Ok..so you are fine now?? or are you still asking something?

dof2001
Starting Member

4 Posts

Posted - 2010-03-03 : 12:27:29

i am getting this error message:
Msg 156, Level 15, State 1, Procedure CreateDataRecord, Line 12
Incorrect syntax near the keyword 'FROM'.[/b]
[code]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[CreateDataRecord]
ON [dbo].[MainForm]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO DataForm (ID, DataID)
SELECT ID, ProjectID, FROM inserted

END
GO
[\code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:34:10

quote:
Originally posted by dof2001

i am getting this error message:
Msg 156, Level 15, State 1, Procedure CreateDataRecord, Line 12
Incorrect syntax near the keyword 'FROM'.[/b]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[CreateDataRecord]
ON [dbo].[MainForm]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO DataForm (ID, DataID)
SELECT ID, ProjectID, FROM inserted

END
GO

did you notice my suggestion? remove the unwanted , at the end of column list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dof2001
Starting Member

4 Posts

Posted - 2010-03-03 : 12:45:32

ups! i missed that

It works now. Thank you all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:51:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources