Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Insert / Update Trigger To Validate PARENT Foreign Keys

    My data structure at the moment looks like this:


    Click image for larger version. 

Name:	Untitled.png 
Views:	31 
Size:	11.0 KB 
ID:	41578

    Regarding table [tblEstimates] and referential integrity I need to make sure that its foreign keys originate form the same Job, eg tblSubcontractors.JobFK = tblSov.JobFK. I can do this pragmatically in the front end app but if I can ensure the integrity in the back end sql server I'd sleep better at night.

    I'm new to sql server and am trying to get my head wrapped around triggers.

    So far I think I've gathered that this can be accomplished in a INSTEAD OF INSERT, UPDATE. I can't get my head around the t sql to solve this, my roadblock is how to use the trigger to validate each row when multiple rows are inserted/deleted. Not sure how all that works. Can I validate and commit individual rows or will I have to cancel all the updates if just one of them is invalid?

    This is about the closest solution I've found for working with multiple rows but I can't figure out the syntax. If that's the correct road to be going down I could use some help decoding how that works.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is a takeOff? Sov?
    Can you describe in simple English (2 or 3 lines)what your graphic represents?
    Just trying to put the pieces into context:
    Job, Estimate, SubContractor---Sov, TakeOff

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Sov is a construction industry term, its needed to group sections of work together. Eg: Landscape

    A takeoff is the qty of work counted (taken off) from blueprints. Eg: 100 sqft of concrete

    The estimate is the underlying estimated costs to perform the "taken off" qty of work. Eg: 100 labor hours

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK. So where do subcontractors fit? Does a subcontractor do 1 or more SOVs that relate to a JOB? Or do subcontractors do takeoffs, which is what I think your post suggests? Your database is about estimates - the bottom line is what exactly are you estimating -takeoff per subcontractor? I don't see any cost figures, so we're not talking estimated cost per job?
    If you are working with multiple inserts and/or deletes, you may want to investigate Transaction Processing to ensure such transactions are completed or discarded as a group.
    Also, re your referenced document, I am not a SQL Server person.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Well just getting back to the original question what I'm trying to figure out is how to use a trigger in SQL Server to maintain referential integrity in the parents (or rather grandparents??).
    I need to make sure that an [Estimate->Subcontractor.JobFK] = [Estimate->Takeoff->Sov.JobFK].

    Let's say multiple rows are updated at once, I'm not understanding how or if I can validate each row's parent's foreign key's individually in t sql or if I have to just invalidate the whole updated recordset if just one record is invalid... which I suppose is acceptable, I'm kinda thinking I can figure that trigger code out.

    It's an easy enough problem to solve in vba, I could even do it with table macros in access but I'm trying to learn sql server.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As I said, I am not a sql server person, so will bow out in hope that someone with sql server technical advice will respond.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    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.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    think I would need to see some example data of what you are trying to do - as in particular I can't see the scope of

    Let's say multiple rows are updated at once
    Update? insert? one table? many tables? From what? a single row of data?

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    1. I think you need a table tblSubcontractors: SubcontractorPK, SubcontractorName, and table tblSubcontractorJobs: SubcontractorJobPK, SubcontractorFK, JobFK (a subcontractor can have several jobs is given one, and probably same job can be given to several subcontractors.

    2. When a job has single subcontractor, then the current structure will probably do. When several, then you will need additional table, where you split Sov between subcontractors (like tblSubcontractorSov: SubcontractorSovPK, SubcontraktorFK, SovFK, SomeMeasureOfWork) and you must have SubcontractorSovFK in tblTakeoffs instead of SovFK.

    3. For avoiding entries with insufficient data (orphan entries) outside validation of entries in form's BeforeUpdate event, the easiest way is to define non-Null (unique) indexes in tables - i.e. record is not saved when some key filed is empty.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    think I would need to see some example data of what you are trying to do - as in particular I can't see the scope of

    Update? insert? one table? many tables? From what? a single row of data?
    Just trying to catch any inserts or updates to tblEstimates' foreign key values to make sure any changes to the "grandchild" table don't allow me to get jobs mixed up.

    This is just a very simplified view of the tables to illustrate the original question. I'm just trying to get my head around how to approach scripting in t-sql to lookup parent tables and handle processing updating one record or many records at a time. The solution I've proposed above does seems to be working though. Some more todo on that is I need to make sure updates don't just mismatch jobs but also I need to check that even if they match they need to remain on the same job as the [deleted] table references. I also have to lock down a few of the parent tables to make sure they can't be 're-referenced' incorrectly as well. But now that I have a working model of how to approach it it should be pretty straightforward to write those triggers. That is unless you guys see glaring problem with the t-sql above?

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can I safely use ROLLBACK TRANSACTION in the AFTER UPDATE events to handle this kind of thing, even if I'm not explicitly defining transactions before the action queries? It looks like each INSERT, UPDATE, DELETE statement gets it's own transaction by default unless a script or procedure explicitly defines one, right?

    Any dangers with something like this?:

    Code:
    ALTER TRIGGER trg_tblTest_AfterUpdate
    ON tblTest
    AFTER UPDATE
    AS
    BEGIN
        IF EXISTS(SELECT ID FROM inserted WHERE fld2='hi')
        BEGIN
            Raiserror('Invalid field value. Rolling back transaction.', 16, 1)
            ROLLBACK TRANSACTION
        END
    END

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Regret I can't answer the question. My concern with triggers is the risk of cascades so I avoid them.

    My preference is to validate the data before inserting/updating

  13. #13
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you elaborate on what you mean by cascade risk?

    Do you mean into something like 'rolling back' past the immediate action query into previous statements?

    (Side note: I'm really enjoying playing with sql server. I wish I decided to check out the free version a while ago)

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    cascade risk
    easier if you read this link https://www.red-gate.com/simple-talk...threat-menace/

    A friend is a very seasoned sql server developer - he won't touch them.

  15. #15
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Thank you for the good read!

    I've already written a bunch of these triggers for this db and so far everything seems to be working as I expect. And they really are quite simple after I've gotten my head around what I'm doing, I've managed to really simplify the code proposed in #7. In my mind's eye these triggers essentially are enforcing a kind of referential integrity on steroids. None of these triggers do any editing of the existing data, they only block bad data. Hopefully the fact that they don't edit or delete will mitigate the downsides mentioned in the article.

    My pseudo code formula has boiled down to
    Code:
    AFTER INSERT, UPDATE
    IF (some sql query based on new data returns mismatching foreign keys from parents, grandparents, etc.)
       raise error
       rollback transaction
       return
    I haven't gotten to utilizing stored procedures (yet) and haven't implemented any explicit transaction batches so I don't know if I should expect problems there when/if I do with the 'ROLLBACK TRANSACTION's in the triggers.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Foreign keys
    By Accessuser67 in forum Access
    Replies: 1
    Last Post: 10-05-2015, 06:33 PM
  2. primary and foreign keys?
    By themebuddies in forum Access
    Replies: 1
    Last Post: 12-14-2014, 09:23 PM
  3. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  4. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  5. Need help with foreign keys
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 03:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums