February 10, 2009 at 10:06 am
I have an XML updating multiple tables, i have no control over this. i need to design a trigger that fires if values in each of these tables match my criteria, can anyone help?
this trigger does not work and i assume its because the ID value in inserted does not yet exist in the other tables
use TEST1
go
CREATE TRIGGER AwaitInfoStatChange
ON Casefile
AFTER INSERT
AS
update casefile
set casefile.casefilestatusid = 4
FROM inserted INNER JOIN
Searches ON CaseFile.CaseFileID = Searches.CaseFileID INNER JOIN
[Plan] ON CaseFile.PlanID = [Plan].PlanID INNER JOIN
Product ON Searches.ProductID = Product.ProductID INNER JOIN
CaseFile ON CaseFile.CaseFileID = inserted.CaseFileID
WHERE (Product.Addressing = 'MasterMap') AND ([Plan].PolyEastings = N'') AND ([Plan].PolyNorthings = N'') AND (CaseFile.CaseFileStatusID = 1) AND (CaseFile.CaseFileid = Inserted.CaseFileid)OR
(Product.Addressing = N'Map') AND ([Plan].PointEastings = N'') AND ([Plan].PointNorthings = N'') AND (CaseFile.CaseFileStatusID = 1) AND (CaseFile.CaseFileid = Inserted.CaseFileid) OR
(Product.Addressing = N'MasterMap') AND ([Plan].PolyEastings = N'0') AND ([Plan].PolyNorthings = N'0') AND (CaseFile.CaseFileStatusID = 1) AND (CaseFile.CaseFileid = Inserted.CaseFileid) OR
(Product.Addressing = N'Map') AND ([Plan].PointEastings = N'0') AND ([Plan].PointNorthings = N'0') AND (CaseFile.CaseFileStatusID = 1) AND (CaseFile.CaseFileid = Inserted.CaseFileid)
go
any help would be very much appreciated
February 10, 2009 at 1:13 pm
[font="Verdana"]You're probably right as to why your trigger isn't working. I suggest you remove the joins.
Also, look at a different solution than a trigger. A trigger is the solution of last resort.
[/font]
February 11, 2009 at 2:17 am
Thanks for your reply, its good to know I can stop chasing my tail.
This leaves me with a bit of a problem, i want to change the status asap because that time is recorded and used in Management Information systems, i could run a scheduled job in SQL Server Agent every 10 mins but i'm worried about the impact on performance.
are there any other methods i haven't thought of?
cheers
L.
February 12, 2009 at 12:03 pm
[font="Verdana"]Is there any reason why you can't use a stored procedure to do your inserts?
Create a stored procedure to which you pass the XML. It does the insert, then updates the status (all within one transaction, and include error handling and rollback for where one of the inserts fails.)
I refer to this as a "gateway procedure", and it's my recommended approach to creating a data change interface for an application.
[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply