From: Michael Dykman
Date: October 17 2009 6:25pm
Subject: Re: Error - "Select Column Not Found Within Trigger"
List-Archive: http://lists.mysql.com/mysql/219087
Message-Id: <814b9a820910171125m5d74f4d9ne86042fcce0658a3@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

It appears to be a simple enough error message. =A0Here is your trigger
you are reffering quite explicitly to credits.enabled:

> =A0select SUM(credits.amount) into total_credits from credits where
> credits.enabled=3D1 and account=3Dnew.account;

and this table has no such column defined. =A0debits does, but not this one

> CREATE TABLE `credits` (
> =A0`account` int(11) NOT NULL,
> =A0`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
> =A0`line_number` int(10) unsigned NOT NULL auto_increment,
> =A0`amount` int(11) NOT NULL default '0',
> =A0`product_code` int(11) NOT NULL default '0',
> =A0`rep_id` int(11) NOT NULL default '0',
> =A0`description` text collate utf8_bin NOT NULL,
> =A0PRIMARY KEY =A0(`line_number`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_bin AUTO_INCREMEN=
T=3D1 ;


=A0- michael dykman


On Sat, Oct 17, 2009 at 2:20 PM, Dan Saul <daniel.saul@stripped> wrote:
> This is the first time I have attempted to expand beyond basic sql for
> storing data in a table like structure. So my level of knowledge is
> "familiar with basic SQL, but lacking in expanded knowledge". I expect my
> error is a newbie mistake.
>
> I have attempted to create a trigger to update another table with the
> calculated balance of two columns in two different tables. Monetary amoun=
ts
> are stored in integer form so $6.59 would be represented as 659. The goal
> here is to move more of the funtionality into SQL where the data can "man=
age
> itself" and away from application code.
>
> The following trigger inserts fine, however upon adding a row to the
> "trigger-enabled table" it recieves an error. The row inserts fine as can=
 be
> proved by selecting the table afterwards, but the trigger operation did n=
ot
> complete.
> *
> The following is my trigger:*
>
>
> create trigger billing.update_account_balance after insert on billing.deb=
its
> for each row
> begin
> =A0declare total_debits int;
> =A0declare total_credits int;
> =A0declare total_balance int;
>
> =A0select SUM(debits.amount) =A0into total_debits =A0from debits =A0where
> debits.enabled=3D1 =A0and account=3Dnew.account;
> =A0select SUM(credits.amount) into total_credits from credits where
> credits.enabled=3D1 and account=3Dnew.account;
>
> =A0set total_balance =3D total_credits - total_debits;
>
> =A0update accounts set accounts.balance =3D total_balance where
> number=3Dnew.account;
> end
>
> *The following is the error as described by PHPMyAdmin:*
>
> *SQL query:*
>
> INSERT INTO `billing`.`debits` ( `account` ,
> `date` ,
> `line_number` ,
> `amount` ,
> `product_code` ,
> `rep_id` ,
> `description` ,
> `enabled`
> )
> VALUES ( '1', '2009-10-04 13:05:14', NULL , '91', '3', '1', 'Service /
> Support', '1'
> )
>
> *MySQL said:*
> =A0#1054 - Unknown column 'credits.enabled' in 'where clause'
>
> *The following is my database structure exported by PHPMyAdmin:*
>
> CREATE TABLE `accounts` (
> =A0`number` int(10) unsigned NOT NULL auto_increment,
> =A0`balance` int(11) NOT NULL default '0',
> =A0PRIMARY KEY =A0(`number`)
> ) ENGINE=3DMyISAM =A0DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_bin AUTO_INCRE=
MENT=3D2 ;
>
> CREATE TABLE `credits` (
> =A0`account` int(11) NOT NULL,
> =A0`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
> =A0`line_number` int(10) unsigned NOT NULL auto_increment,
> =A0`amount` int(11) NOT NULL default '0',
> =A0`product_code` int(11) NOT NULL default '0',
> =A0`rep_id` int(11) NOT NULL default '0',
> =A0`description` text collate utf8_bin NOT NULL,
> =A0PRIMARY KEY =A0(`line_number`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_bin AUTO_INCREMEN=
T=3D1 ;
>
> CREATE TABLE `debits` (
> =A0`account` int(11) NOT NULL,
> =A0`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
> =A0`line_number` int(10) unsigned NOT NULL auto_increment,
> =A0`amount` int(11) NOT NULL default '0',
> =A0`product_code` int(11) NOT NULL default '0',
> =A0`rep_id` int(11) NOT NULL default '0',
> =A0`description` text collate utf8_bin NOT NULL,
> =A0`enabled` tinyint(1) NOT NULL default '1',
> =A0PRIMARY KEY =A0(`line_number`)
> ) ENGINE=3DMyISAM =A0DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_bin AUTO_INCRE=
MENT=3D9 ;
>
> DROP TRIGGER IF EXISTS `billing`.`update_account_balance`;
> DELIMITER //
> CREATE TRIGGER `billing`.`update_account_balance` AFTER INSERT ON
> `billing`.`debits`
> =A0FOR EACH ROW begin
> =A0declare total_debits int;
> =A0declare total_credits int;
> =A0declare total_balance int;
>
> =A0select SUM(debits.amount) =A0into total_debits =A0from debits =A0where
> debits.enabled=3D1 =A0and account=3Dnew.account;
> =A0select SUM(credits.amount) into total_credits from credits where
> credits.enabled=3D1 and account=3Dnew.account;
>
> =A0set total_balance =3D total_credits - total_debits;
>
> =A0update accounts set accounts.balance =3D total_balance where
> number=3Dnew.account;
> end
> //
> DELIMITER ;
>
> CREATE TABLE `products` (
> =A0`id` int(11) NOT NULL auto_increment,
> =A0`description` text collate utf8_bin NOT NULL,
> =A0`daily_charge` int(11) NOT NULL default '0',
> =A0PRIMARY KEY =A0(`id`)
> ) ENGINE=3DMyISAM =A0DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_bin AUTO_INCRE=
MENT=3D4 ;
>
>
> Thank you in advance.
>
> Dan
>



--
=A0- michael dykman
=A0- mdykman@stripped

"May you live every day of your life."
=A0 =A0Jonathan Swift



--=20
 - michael dykman
 - mdykman@stripped

"May you live every day of your life."
    Jonathan Swift