Jump to content
  • 0

Trigger to Either Update Matching Record in Another Table or Create New Record


kpcollier
 Share

Question

Below is my trigger that is currently not working.

This is for newly inserted records in the Equipment_Inspection_Table. Pretty much, if Equipment_Inspection_Table.Serial# (#inserted.Serial_Num) matches Equipment_Table.Serial#, just update a few fields. If not, create a new record with the information supplied from #inserted into Equipment_Table.

eitrigger.thumb.PNG.1ca3a7d7b7ae94e2f3df9e2bc8d39617.PNG

 

The trigger is on Equipment_Inspection_Table. When a new record is inserted to this table, I need to query the Equipment_Table and see if there is a matching record. If there is, I need to update the information. If the newly inserted record does not match anything in the Equipment_Table, then I need to add it. 

The setup above doesn't seem to do either. 

Any help is appreciated.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

I was able to accomplish this by getting rid of the If/Then block. I still do not understand why that block didn't work the way I expected, and not sure if the 'in' part even works. 

This forum post helped me with the 'insert new record if value is not found' part. Then, I just made an Update block with a join on #inserted and Equipment_Table, on Serial #. If there is a match, it will update it. If not, the update block is ignored. 

 

Link to comment
Share on other sites

  • 0

I tend to avoid using if then blocks and In/not in statement.

I'm not sure as well but they tend to file sometimes. On cases like this, Id rather use multiple blocks with redundant where clauses. Id rather use 'exist/not exist'  as well instead of in and not in. They are a bit confusing at first but they never failed me so far. 

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...
 Share

×
×
  • Create New...