CODEWITHSUNDEEP
mysqltriggers
George
George

Reputation: 1114

Using a trigger on table in mysql

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.

Upvotes: 2

Views: 1185

Answers (2)

Duahs
Duahs

Reputation: 146

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.

Upvotes: 1

ethrbunny
ethrbunny

Reputation: 10469

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")

Upvotes: 0

Related Questions