MySQL Forums
Forum List  »  Triggers

Re: Trigger Creation Help?
Posted by: Peter Brawley
Date: November 06, 2016 03:24PM

It's a terrific idea to read the documentation for features you're unfamiliar with.

As the MySQL manual page for triggers makes clear, a Trigger may not execute UPDATE, INSERT or DELETE commands on the table on which it's defined, and it must refer to that table's column values as NEW.colname and OLD.colname.

Is this what you mean?

drop trigger if exists playreg_after_ins;
delimiter go
create trigger playreg_after_ins after insert on playerreg for each row
begin
  select transferno, transferto, created into @transferno, @club, @updated_on
  from transfers
  where transfers.playerregno = NEW.playerregno;
  if @transferno is not null and @club is not null and @updated_on is not null then
    set NEW.shortcode=@transferno, NEW.club=@club, NEW.updated_on=@updated_on;
  end if;
end;
go
delimiter ;

But copying data that already exists is redundant, therefore most always a consequence of a database design error. So long as the playerreg and transfers tables connect via a key, there should never be a need to copy more than the key.

Options: ReplyQuote


Subject
Views
Written By
Posted
1322
November 06, 2016 02:34PM
Re: Trigger Creation Help?
528
November 06, 2016 03:24PM
486
November 06, 2016 03:33PM


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.