Using a trigger on table in mysql

Question

I am creating a trigger on the table below,

  CREATE TABLE test( id int auto_increment primary key,
              name varchar(40),
              debit decimal(19,2) default "0.00",
              fee decimal(19,2) default "0.00",
              balance decimal(19,2) default "0.00")
              ENGINE=INNODB;

Below also is the trigger code.

CREATE TRIGGER update_test BEFORE INSERT ON test t FOR EACH ROW
(select debit from test d where d.id="1")  
SET new.balance=30 + d.debit;

This trigger is suppose to update the test table(balance column) upon insertion into the test table.The trigger is suppose to select the debit value from the test table and add 30 before updating the balance column in the test table.But this query above wouldn't work.It keeps giving me this error

ERROR 1415 (0A000): Not allowed to return a result set from a trigger

Please I need help on how to figure what the problem is.Thanks in advance.

what is the actual trigger body? What you showed is only an extract. Also, I'm not sure of your logic. Do you realise that select debit from test d will return more than one row, right? — Sebas
– Sebas, Commented Jul 18, 2013 at 13:37
Please I have Edited my trigger code.I am doing this in mysql 5.5. Thanks — George
– George, Commented Jul 18, 2013 at 15:43

Duahs · Accepted Answer · 2013-07-18 18:44:42Z

1

This will work.

CREATE TRIGGER update_test BEFORE INSERT ON test FOR EACH ROW
set @d=(select debit from test where id="1"),  
new.balance= 30 + Coalesce(@d,0);

The above code will work,try it and tell me your responds.

answered Jul 18, 2013 at 18:44

Duahs

1462 gold badges3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ethrbunny · Accepted Answer · 2013-07-18 18:21:53Z

0

Try this:

CREATE TRIGGER update_test BEFORE INSERT ON test t FOR EACH ROW
  SET new.balance=30 + (select debit from test d where d.id="1")

answered Jul 18, 2013 at 18:21

ethrbunny

10.5k9 gold badges77 silver badges145 bronze badges

1 Comment

George Over a year ago

Thanks for your effort

Collectives™ on Stack Overflow

Using a trigger on table in mysql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related