I'm not really sure if this is the best way to do this so I'm hoping someone can give me the thumbs up/thumbs down but I've come up with the following trigger
Code:
CREATE TRIGGER trg_tblEstimates_InsertUpdate
ON tblEstimates
INSTEAD OF INSERT, UPDATE
AS
BEGIN
DECLARE @InvalidReferences INT;
--This query joins the inserted table with all the parents and counts rows
--with mismatching Job foreign keys.
SELECT @InvalidReferences = Count(inserted.EstimatePK)
FROM ( tblSubcontractors
INNER JOIN inserted
ON tblSubcontractors.SubcontractorPK = inserted.SubcontractorFK
)
INNER JOIN (tblSov
INNER JOIN tblTakeoffs
ON tblSov.SovPK = tblTakeoffs.SovFK)
ON inserted.TakeoffFK = tblTakeoffs.TakeoffPK
WHERE tblSubcontractors.JobFK <> tblSov.JobFK;
--Abort INSERTS if any mismatched job foreign keys were found
IF (@InvalidReferences > 0)
BEGIN
-- no good, abort insert
Raiserror('Invalid parent references. Insert aborted.', 16, 1)
RETURN
END
-- If we made it here the references checked out so we can finally insert/update the records
-- figure out if this is an insert or update
IF EXISTS(SELECT * FROM deleted)
BEGIN
-- this must be an update
UPDATE
tblEstimates
SET
tblEstimates.EstimateQuantity = inserted.EstimateQuantity
, tblEstimates.EstimateDescription = inserted.EstimateDescription
, tblEstimates.EstimateRate = inserted.EstimateRate
, tblEstimates.BillableItemFK = inserted.BillableItemFK
, tblEstimates.TakeoffFK = inserted.TakeoffFK
, tblEstimates.SubcontractorFK = inserted.SubcontractorFK
FROM
tblEstimates
INNER JOIN
inserted
ON
tblEstimates.EstimatePK = inserted.EstimatePK;
END
ELSE
BEGIN
-- this must be an insert
INSERT INTO tblEstimates (EstimateQuantity, EstimateDescription, EstimateRate, BillableItemFK, TakeoffFK, SubcontractorFK)
SELECT EstimateQuantity, EstimateDescription, EstimateRate, BillableItemFK, TakeoffFK, SubcontractorFK
FROM inserted;
END
END
The idea is I'm joining the [inserted] table against all the foreign key tables and counting the rows where the JobFK is incorrect. If the count is greater than zero I'll abort all inserts/updates and throw an error. Is this approach sound? Any problems with my code above? ... this is my first day messing with sql server.
Is there a way to filter out invalid rows and just save the valid ones?
[EDIT]
*smacks forehead* to filter out the bad ones and save the good ones just use the select query above in a subquery to filter out the bad rows from the inserted table!!!! right?
Okay... but should I? Now I'm thinking I probably would want to abort all the rows even if only some of them are bad to force the user (front end designer, still me) to pay better attention.