Solved

insert update triger Ms SQL 2005

Posted on 2010-08-12
16
300 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:QuinnDester
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
16 Comments
 
LVL 5

Expert Comment

by:tvPrasad
ID: 33423355
/****** 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
 
LVL 1

Expert Comment

by:nickrawlins
ID: 33423371
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
 
LVL 5

Expert Comment

by:tvPrasad
ID: 33423545
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33423580
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33423608
> for updates use UPDATE table to find updates

Don't be silly, there are only 2 virtual tables INSERTED and DELETED.
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 33423745
@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
 
LVL 3

Author Comment

by:QuinnDester
ID: 33424017
@cyberkiwi:

sorry that isnt working either, the table is inserting and updating correctly, but the trigger isnt firing to update other records,
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 33424176
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33424251
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
 
LVL 3

Author Comment

by:QuinnDester
ID: 33424501
there is actualy 8 records it should match with, i have cecked and they are all there
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33424522
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
 
LVL 3

Author Comment

by:QuinnDester
ID: 33424683
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33424724
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
 
LVL 3

Author Comment

by:QuinnDester
ID: 33424798
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
 
LVL 1

Expert Comment

by:nickrawlins
ID: 33424926
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33424948
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question