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

[kpcollier]

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.

 

 

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.

[kpcollier]

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. 

 

[NiceDuck]

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.