Jump to content
  • 0

Trigger Debugging


pmcfarlain

Question

I need some help figuring out the issue with my trigger. Currently, it inserts nothing into my tbl_ggl_job_lines_dates table. In previous iterations, it has inserted duplicates of the same record. What it does is make a line from the feeder table which is this #inserted table. Then it should use this trigger to create a matching line in the dates table with some info filled in from the feeder table. The feeder table only has the Job ID which can have many Line Numbers within it. The dates table only has the Line Number. The #inserted table has both. Thus the two joins. 

The where statement is supposed to ensure that I don't pick up duplicate records but I'm not picking up any records.

17672465_ScreenShot2021-07-08at12_38_50PM.thumb.png.4c5b482f02850037893e1e580dbd436b.png

Any help would be appreciated. Thanks!

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 1

I think the first issue I see is with the 2nd join and the WHERE clause. The 2nd join is filtering to only include records where Line Number = Line Number. Then the WHERE clause is selecting out of that filtered group where Line Number != Line Number. Since the 2nd join only gives us records where Line Number = Line Number, there will never be a record found with that WHERE clause.

I'm not exactly sure the best solution for your problem. But, personally, I would include a different foreign key in your dates table, like an ID field. Have the 2nd join run off of that ID field to match the records, and then use the WHERE clause to make sure it isn't a duplicate on Line Number. 

Link to comment
Share on other sites

  • 0
21 minutes ago, kpcollier said:

I think the first issue I see is with the 2nd join and the WHERE clause. The 2nd join is filtering to only include records where Line Number = Line Number. Then the WHERE clause is selecting out of that filtered group where Line Number != Line Number. Since the 2nd join only gives us records where Line Number = Line Number, there will never be a record found with that WHERE clause.

I'm not exactly sure the best solution for your problem. But, personally, I would include a different foreign key in your dates table, like an ID field. Have the 2nd join run off of that ID field to match the records, and then use the WHERE clause to make sure it isn't a duplicate on Line Number. 

Shouldn't the FULL OUTER join include all records from the tables though?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...