insert update triger Ms SQL 2005

i can not get this trigger to update on an insert or update on MS SQL 2005

can anybody see what is wrong with it.


edited for further explination

the database is updayed via a .net web service, the data base updates correctly, but is not firing the trigger that should update the status column on all records in the table where [name] = inserted record name.
/****** Object:  Trigger [dbo].[status_update]    Script Date: 08/12/2010 17:57:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[status_update] ON [dbo].[Planets] 
FOR INSERT,UPDATE
AS 
UPDATE dbo.Planets 
SET planets.[status] = inserted.[status] 
FROM planets join inserted  on planets.Coordinates = inserted.Coordinates
WHERE planets.[name] = inserted.[name]

Open in new window

LVL 3
QuinnDesterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tvPrasadCommented:
/****** Object:  Trigger [dbo].[status_update]    Script Date: 08/12/2010 17:57:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[status_update] ON [dbo].[Planets]
AFTER INSERT,UPDATE
AS
UPDATE dbo.Planets
SET planets.[status] = inserted.[status]
FROM planets join inserted  on planets.Coordinates = inserted.Coordinates
WHERE planets.[name] = inserted.[name]

USE AFTER INSTEAD OF FOR
0
nickrawlinsCommented:
hmm, do you need to do a join? can you not do this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[status_update] ON [dbo].[Planets]
FOR INSERT,UPDATE
AS
UPDATE dbo.Planets
SET planets.[status] = inserted.[status]
FROM planets where planets.Coordinates = inserted.Coordinates
AND planets.[name] = inserted.[name]
0
tvPrasadCommented:
One more thing you need seperate insert from update since this Trigger is for both

for updates use UPDATE table to find updates

Hope it helps
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

cyberkiwiCommented:
Your web service may not be reporting errors to you.
If you have recursive triggers turned on, it will perpetually try to update the status until it aborts.

See modification below.
/****** Object:  Trigger [dbo].[status_update]    Script Date: 08/12/2010 17:57:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[status_update] ON [dbo].[Planets] 
FOR INSERT,UPDATE
AS 
UPDATE dbo.Planets 
SET planets.[status] = inserted.[status] 
FROM planets join inserted  on planets.Coordinates = inserted.Coordinates
WHERE planets.[name] = inserted.[name]
  AND planets.[status] <> inserted.[status] 

Open in new window

0
cyberkiwiCommented:
> for updates use UPDATE table to find updates

Don't be silly, there are only 2 virtual tables INSERTED and DELETED.
0
QuinnDesterAuthor Commented:
@nickrawlins:

ALTER TRIGGER [dbo].[status_update] ON [dbo].[Planets]
FOR INSERT,UPDATE
AS
UPDATE dbo.Planets
SET planets.[status] = inserted.[status]
FROM planets where planets.Coordinates = inserted.Coordinates
AND planets.[name] = inserted.[name]

this wouldnt work, the Coordinates only match the one record.


@tvPrasad: that isnt working either....
0
QuinnDesterAuthor Commented:
@cyberkiwi:

sorry that isnt working either, the table is inserting and updating correctly, but the trigger isnt firing to update other records,
0
QuinnDesterAuthor Commented:
More information, i have tried a manual update and this shows that the trigger is firing but not updating the table..

this is the result i am getting..


(0 row(s) affected)

(1 row(s) affected)
0
cyberkiwiCommented:
That can only mean that it has found no matching record (by name) to update...
Can you try inserting the same name twice in a row?
0
QuinnDesterAuthor Commented:
there is actualy 8 records it should match with, i have cecked and they are all there
0
cyberkiwiCommented:
Regardless, could you please just try it?

insert planets(name, coordinates, status)
values ('easilyidentifiable', <whatever type>, 1)
go
insert planets(name, coordinates, status)
values ('easilyidentifiable', <whatever type>, 1)
go
0
QuinnDesterAuthor Commented:
i did this

insert into planets ( [name], [status]) values ('test','active')
insert into planets ( [name], [status]) values ('test','off')
(0 row(s) affected)

(1 row(s) affected)
(0 row(s) affected)

(1 row(s) affected)

select [name], [status] from planets where [name] like 'test'

test      active
test      off

the trigger should have changed the first record to off when the seconed record was inserted
0
cyberkiwiCommented:
Since you don't care about the coordinates, why join by that condition?
/****** Object:  Trigger [dbo].[status_update]    Script Date: 08/12/2010 17:57:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[status_update] ON [dbo].[Planets] 
FOR INSERT,UPDATE
AS 
UPDATE dbo.Planets 
SET planets.[status] = inserted.[status] 
FROM inserted
WHERE planets.[name] = inserted.[name]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QuinnDesterAuthor Commented:
that did it...

i was joining on the cords because that was the only unique identifier i had, but i see now that it was limiting the results to those it could join on, and that would be 1 record


thanks for your help
0
nickrawlinsCommented:
In all due respect, my answer above was identical to cyberkiwi's and was the second comment yet the points were assigned to him... ?
0
cyberkiwiCommented:
Nick,
With all due respect, your query is no different from the question.  Putting that in the JOIN vs WHERE still enforces the filter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.