Re: trigger mysql 5

[sheeri kritzer]

You have to change the delimiter you use, otherwise MySQL treats the ;
as the end of the line and processes the commands.  When I tried to
run your trigger (with the standard delimiter of ;) I got:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'update trans set trans_status=NEW.trans_status,
trans_state=NEW.trans_state
wher' at line 5
mysql> END IF;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'END IF' at line 1
mysql> END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'END' at line 1

(Had you posted the actual error code, it would have been much nicer).

So, yeah, you need to set the delimiter.

-Sheeri

On 5/18/06, Frederic Belleudy <smaugzone@stripped> wrote:
> Hi all!
> server version: 5.0.18
>
> I attempted to create a trigger with statement and update, it does like
> this:
> CREATE TRIGGER update_trans AFTER UPDATE ON main_trans
> FOR EACH ROW
> BEGIN
> IF OLD.trans_status='INITIAL' and OLD.trans_state='INITIAL' THEN
>  update trans set trans_status=NEW.trans_status, trans_state=NEW.trans_state
> where main_trans_id=NEW.main_trans_id;
> END IF;
> END;
>
> I 'm getting an error after : update trans set ...........
> I tried to insert BEGIN update trans set ...... END
>
> Note that I'm creating the trigger directly in phpmyadmin, I already created
> succesfully a trigger but it was really a simple one...
> I did try the delimiter "$$" or any other delimiter of my choice, but it
> doesn't accept the DELIMITER declaration before my create trigger....
>
> Nothing works.... Any tips!? Is that possible to do this kind of trigger!?
>
> Tks for your answers, I appreciate!!
>
>