Help with trigger

Hi…
I am using mysql database, i want to calculate the value using trigger.

BEGIN insert into all_report VALUES(new.chk_id, new.date, new.jam, new.chk_id, new.code_item, new.nama_item, new.qty, new.harga, new.diskon, new.total, new.customer, new.no_member, new.payment_type, new.waiter); UPDATE all_stock SET all_stock.anggrek=anggrek-new.qty WHERE all_stock.code_item=new.code_item; UPDATE all_stock SET all_stock.total_stock=anggrek+astonpas+arion+astontpc+balkon+bs+fg+fourpoints+hs+indigo+jp+mccl+mcc+papaya+pfgl+stbd+tprd+transcellar WHERE all_stock.code_item=new.code_item; END

the code is working, trigger created via phpmyadmin, but sometimes its always failed because of the trigger definer’s name, which is has to be changed every time do some trigger editing.

my question is, is it possible to convert it into xojo code.
if there are some sample project would be great.

thanks
regards,
arief

What you show is only a part of a trigger, not the complete one. Triggers are stored on the Server and triggered by another action. There is no need to convert something into xojo code, because the Frotend (Xojo) normally doesnt know anything about server-side stored programs.
To initially create a trigger from within xojo you can do that by the same sql-code that is used in phpmyadmin etc. to do that.

can you explain that ? once a trigger is defined, it is always executed according to its parameters ?

while do editing the Trigger, the definer’s name is automatically change the name into root@localhost, and its shows mysql error #1227. (access denied)
normally, while creating new trigger, the definer just automatically generated by phpmyadmin with name like cpsz… something.

thanks
regards,
arief

Where is your ‘create trigger’ code?

When I create triggers in SQL Server, the trigger belongs to the user that creates it.
That user needs access to the tables to which it refers.

To ensure the trigger (or view…) is ‘in the right domain’, the create statement would include the intended owner

eg if you log in as user dbo, and create a view or trigger called mything which acts on table mytable, then as long as mytable is visible to the dbo user, you code will say

insert into mytable where...

But if the owner of ‘mytable’ is user myapp, you may need to be more explicit:

create trigger  myapp.mytrigger   as   insert into myapp.mytable where ...

Is it the same for mysql?

Here’s the screenshot of the trigger, I made it in every table of mysql database via phpmyadmin.

Screenshot
thanks
regards,
arief

If phpmyadmin changes the definer this is a problem with the privileges in mysql.
Back to your question:

I don’t know what you want to convert. There is only on syntax for creating triggers. It is the same in phpmyadmin, xojo and everywhere else. It is the mysql SQL-language.
This is an example:

If you need to use the “;” within your trigger, you have to change the delimiter first:

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
delimiter ;

In xojo you could execute that the same way as every query.

may be you should change the owner of the trigger after editing it ?

https://stackoverflow.com/questions/18593746/how-to-bulk-change-mysql-triggers-definer

yes, its permission problem. now its fixed. and can execute the query from xojo too.

thanks for the helps.

regards,
arief