Stuck on a trigger - 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

Stuck on a trigger

Author

Topic

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-05-05 : 18:27:42

Everything works except for the select portion, I cant put my finger
on what is wrong with this.. I included the trigger and error..
If one can show what I did wrong that would be great..Thanx..

SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[arcit] ON [dbo].[active] AFTER UPDATE
as
begin
begin tran

SET IDENTITY_INSERT Archive ON
INSERT INTO Archive()
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'archive' order by ordinal_position
from Inserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1

DELETE FROM a
from Inserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1

commit tran
end

Error Message

Msg 156, Level 15, State 1, Procedure arcit, Line 10
Incorrect syntax near the keyword 'from'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-05 : 18:30:45

Your code does not make sense. What are you intending to do with INFORMATION_SCHEMA.TABLES?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 01:11:34

quote:
Originally posted by sinjin67

Everything works except for the select portion, I cant put my finger
on what is wrong with this.. I included the trigger and error..
If one can show what I did wrong that would be great..Thanx..

SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[arcit] ON [dbo].[active] AFTER UPDATE
as
begin
begin tran

SET IDENTITY_INSERT Archive ON
INSERT INTO Archive()
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'archive' order by ordinal_position
from Inserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1

DELETE FROM a
from Inserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1

commit tran
end

Error Message

Msg 156, Level 15, State 1, Procedure arcit, Line 10
Incorrect syntax near the keyword 'from'.

Your SELECT clause is wrong. You have two from statements. Can i ask what is your requirement?

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-05-06 : 14:07:03

I am just trying to get the trigger to grab all the column
names instead of typing it. In my test trigger
I have used Select (name, com, address)

its just when their are lots to type I was thinking
their has to be way to select everything for that record
and send it to the archive then delete the original record.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 14:10:16

You can not do that without dynamic SQL. It would not be advisable in a trigger though.

Stop being lazy and type out the columns. You can easily script them from Management Studio or Query Analyzer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-06 : 14:28:37

hey lazy is good....dynamic sql isn't

Generate the code

http://weblogs.sqlteam.com/brettk/archive/2006/08/10/11126.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-05-06 : 18:04:16

Guys, I am just doing my best to learn..
Thanx for the input..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources