MySQL Forums
Forum List  »  Triggers

Re: Trigger HelpPlease
Posted by: Peter Brawley
Date: July 07, 2015 10:26PM

The code suggests that the requirement is to copy `realtime` row inserts and updates to `airfields` if and only if realtime.squawk is between 6160 and <=6167 or 6171 and 6177, and realtime.altitude <= 4000. But the code shown doesn't do that; instead it ignores the current Insert or Update `realtime` column values, and instead executes a query on the rest of the`realtime` table.

If I understand your requirement correctly, the Insert Trigger needs to say something like ...

if (new.squawk between 6160 and 6167 or new.squawk between 6171 and 6177) and new.altitude <= 4000 then
  insert into airfields (col1,col2,squawk,altitude,...) (new.col1,newcol2,new.squawk,new.altitude,...);
end if;

substituting the real column names for col1, col2 etc.

The `realtime` Update Trigger needs to handle five distinct cases ...

(i) old row values match copy criteria, so do the new ones, and they're identical, so do nothing

(ii) old and new values match copy criteria but differ, so update the existing airfields row.

(iii) neither old nor new values match copy criteria, so do nothing

(iv) old row values match copy criteria but the new values do not, so delete the matching airfields row

(v) old row values do not match copy criteria but the new ones do, so insert the row into airfields

Options: ReplyQuote

Written By
July 07, 2015 04:33PM
Re: Trigger HelpPlease
July 07, 2015 10:26PM
July 08, 2015 07:00AM
July 08, 2015 09:42AM
July 15, 2015 03:50AM
July 15, 2015 10:00AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.