May 18, 2009 at 3:07 am
Morning All, I hope this post finds you all well!
I have two very similar problems that would be quickly be resolved by an "on error resume next" - like expression if I were working in vb. Basically I have ten rss feeds that come into table 1 of the database at once and get routed to table 2 via the following triggers:
____________________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tr_item_insert]
on [dbo].[item]
for insert
as
Begin
insert into newsarchive(newspointname, sequence, title, link, description)
select newspointname, sequence, title, link, description from inserted
End
____________________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tr_item_update]
on [dbo].[item]
for update
as
Begin
insert into newsarchive(newspointname, sequence, title, link, description)
select newspointname, sequence, title, link, description from inserted
End
_______________________
The problem is the following: As the trigger sends the ten feeds to table2, if one of the batch of ten feeds violates the primary key, the whole batch gets ignored and no rss feeds get put in at all...
The second is similar: Due to the fact that I wasn't getting any data into table 2, I took out the IX primary key that was causing the problems, the only thing is that I have now found myself with a mound of duplicates obviously. I want to delete these duplicate rows by creating a third table that I move all the data into, deleting the data in table 2, creating an IX primary key and sending the data back to table 2 from table 3 by using the query:
INSERT INTO NewsPoint.dbo.SwitchToNewsarchive (WhenCreated, Title, Viewarticle, Description, NewsPointName, Sequence, Link, NewsID, DossierRef, RelevantSectors, FullArticle)
Select WhenCreated, Title, Viewarticle, Description, NewsPointName, Sequence, Link, NewsID, DossierRef, RelevantSectors, FullArticle
From newspoint.dbo.newsarchive
GO
But I still (unsurprisingly) get the whole process aborted by the Primary Key violation... is there any way such as on error resume next and if so could someone show me the syntax using the code above?
Would be greatly appreciated 😉
Have a good week.
Mark
May 18, 2009 at 6:10 am
monsieurmark777 (5/18/2009)
The problem is the following: As the trigger sends the ten feeds to table2, if one of the batch of ten feeds violates the primary key, the whole batch gets ignored and no rss feeds get put in at all...
Yup. Statements are an all-or-nothing operation. Either the entire statement succeeds or the entire thing fails. It's actually required by the rules of relational databases
But I still (unsurprisingly) get the whole process aborted by the Primary Key violation... is there any way such as on error resume next and if so could someone show me the syntax using the code above?
You need to get rid of the duplicates before you insert or before you put the PK on. SQL won't allow select rows to disappear from an insert.
If the rows you're inserting are full duplicates, you can use distinct.
INSERT INTO NewsPoint.dbo.SwitchToNewsarchive (WhenCreated, Title, Viewarticle, Description, NewsPointName, Sequence, Link, NewsID, DossierRef, RelevantSectors, FullArticle)
Select DISTINCT WhenCreated, Title, Viewarticle, Description, NewsPointName, Sequence, Link, NewsID, DossierRef, RelevantSectors, FullArticle
From newspoint.dbo.newsarchive
As for your triggers, check if the row's in the table and only insert the rows that aren't.
ALTER trigger [dbo].[tr_item_insert]
on [dbo].[item]
for insert
as
Begin
insert into newsarchive(newspointname, sequence, title, link, description)
select newspointname, sequence, title, link, description from inserted
WHERE NOT EXISTS (SELECT 1 FROM newsarchive WHERE newsarchive.<primary key field> = inserted.<primary key field>)
END
As for your update trigger, it's just copying the new version of the rows to the archive. Is that really what you want? To have x copies of a row in the archive that may be almost identical, with no way to tell when they were changed?
Normally an archive table, if it's supposed to keep modifications, would have a date in there to show when the row changed, often the date would be part of the PK.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 7:47 am
Thanks for the Gail, I dropped WHERE NOT EXISTS (SELECT 1 FROM newsarchive WHERE newsarchive.title = inserted.title) into my trigger, but when it executes I get the error:
"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AS" in the equal to operation."
Have checked the DBs and they all seem to be set as "French_CI_AS"...
Any clue why I can't append your line?
Mark
May 18, 2009 at 8:27 am
Evidently the columns included in the where clause have different collations, so you must explicitly specify the collation e.g. WHERE column1= column2 COLLATE SQL_LATIN.....
May 18, 2009 at 10:14 am
monsieurmark777 (5/18/2009)
Have checked the DBs and they all seem to be set as "French_CI_AS"...Any clue why I can't append your line?
Check the server's collation (which affects the system databases), check the collation of the string columns in the various tables. They can be different from the db's default collation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 11:04 am
That all seems to be working great. Big thank you to both of you! If SQL were a swimming pool, I'd have my head under water in the shallow end...
Cheers, Mark
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply