Error - "Select Column Not Found Within Trigger"

--00504502d1891ffd8704762592c4
Content-Type: text/plain; charset=ISO-8859-1

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 amounts
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 "manage
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 not
complete.
*
The following is my trigger:*


create trigger billing.update_account_balance after insert on billing.debits
for each row
begin
declare total_debits int;
declare total_credits int;
declare total_balance int;

select SUM(debits.amount) into total_debits from debits where
debits.enabled=1 and account=new.account;
select SUM(credits.amount) into total_credits from credits where
credits.enabled=1 and account=new.account;

set total_balance = total_credits - total_debits;

update accounts set accounts.balance = total_balance where
number=new.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:*
#1054 - Unknown column 'credits.enabled' in 'where clause'

*The following is my database structure exported by PHPMyAdmin:*

CREATE TABLE `accounts` (
`number` int(10) unsigned NOT NULL auto_increment,
`balance` int(11) NOT NULL default '0',
PRIMARY KEY (`number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;

CREATE TABLE `credits` (
`account` int(11) NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`line_number` int(10) unsigned NOT NULL auto_increment,
`amount` int(11) NOT NULL default '0',
`product_code` int(11) NOT NULL default '0',
`rep_id` int(11) NOT NULL default '0',
`description` text collate utf8_bin NOT NULL,
PRIMARY KEY (`line_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

CREATE TABLE `debits` (
`account` int(11) NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`line_number` int(10) unsigned NOT NULL auto_increment,
`amount` int(11) NOT NULL default '0',
`product_code` int(11) NOT NULL default '0',
`rep_id` int(11) NOT NULL default '0',
`description` text collate utf8_bin NOT NULL,
`enabled` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`line_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=9 ;

DROP TRIGGER IF EXISTS `billing`.`update_account_balance`;
DELIMITER //
CREATE TRIGGER `billing`.`update_account_balance` AFTER INSERT ON
`billing`.`debits`
FOR EACH ROW begin
declare total_debits int;
declare total_credits int;
declare total_balance int;

select SUM(debits.amount) into total_debits from debits where
debits.enabled=1 and account=new.account;
select SUM(credits.amount) into total_credits from credits where
credits.enabled=1 and account=new.account;

set total_balance = total_credits - total_debits;

update accounts set accounts.balance = total_balance where
number=new.account;
end
//
DELIMITER ;

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`description` text collate utf8_bin NOT NULL,
`daily_charge` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;


Thank you in advance.

Dan

--00504502d1891ffd8704762592c4--

Re: Error - "Select Column Not Found Within Trigger"

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 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@gmail.com

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



--=20
- michael dykman
- mdykman@gmail.com

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Error - "Select Column Not Found Within Trigger"

--00504502d189e992f00476262f78
Content-Type: text/plain; charset=ISO-8859-1

Well that is embarasing, thank you, It always ends up being the simple
things that get you.

On Sat, Oct 17, 2009 at 1:25 PM, Michael Dykman wrote:

> It appears to be a simple enough error message. Here is your trigger
> you are reffering quite explicitly to credits.enabled:
>
> > select SUM(credits.amount) into total_credits from credits where
> > credits.enabled=1 and account=new.account;
>
> and this table has no such column defined. debits does, but not this one
>
> > CREATE TABLE `credits` (
> > `account` int(11) NOT NULL,
> > `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
> > `line_number` int(10) unsigned NOT NULL auto_increment,
> > `amount` int(11) NOT NULL default '0',
> > `product_code` int(11) NOT NULL default '0',
> > `rep_id` int(11) NOT NULL default '0',
> > `description` text collate utf8_bin NOT NULL,
> > PRIMARY KEY (`line_number`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
>
>
> - michael dykman
>
>
> On Sat, Oct 17, 2009 at 2:20 PM, Dan Saul 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
> amounts
> > 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
> "manage
> > 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
> not
> > complete.
> > *
> > The following is my trigger:*
> >
> >
> > create trigger billing.update_account_balance after insert on
> billing.debits
> > for each row
> > begin
> > declare total_debits int;
> > declare total_credits int;
> > declare total_balance int;
> >
> > select SUM(debits.amount) into total_debits from debits where
> > debits.enabled=1 and account=new.account;
> > select SUM(credits.amount) into total_credits from credits where
> > credits.enabled=1 and account=new.account;
> >
> > set total_balance = total_credits - total_debits;
> >
> > update accounts set accounts.balance = total_balance where
> > number=new.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:*
> > #1054 - Unknown column 'credits.enabled' in 'where clause'
> >
> > *The following is my database structure exported by PHPMyAdmin:*
> >
> > CREATE TABLE `accounts` (
> > `number` int(10) unsigned NOT NULL auto_increment,
> > `balance` int(11) NOT NULL default '0',
> > PRIMARY KEY (`number`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
> >
> > CREATE TABLE `credits` (
> > `account` int(11) NOT NULL,
> > `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
> > `line_number` int(10) unsigned NOT NULL auto_increment,
> > `amount` int(11) NOT NULL default '0',
> > `product_code` int(11) NOT NULL default '0',
> > `rep_id` int(11) NOT NULL default '0',
> > `description` text collate utf8_bin NOT NULL,
> > PRIMARY KEY (`line_number`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
> >
> > CREATE TABLE `debits` (
> > `account` int(11) NOT NULL,
> > `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
> > `line_number` int(10) unsigned NOT NULL auto_increment,
> > `amount` int(11) NOT NULL default '0',
> > `product_code` int(11) NOT NULL default '0',
> > `rep_id` int(11) NOT NULL default '0',
> > `description` text collate utf8_bin NOT NULL,
> > `enabled` tinyint(1) NOT NULL default '1',
> > PRIMARY KEY (`line_number`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=9 ;
> >
> > DROP TRIGGER IF EXISTS `billing`.`update_account_balance`;
> > DELIMITER //
> > CREATE TRIGGER `billing`.`update_account_balance` AFTER INSERT ON
> > `billing`.`debits`
> > FOR EACH ROW begin
> > declare total_debits int;
> > declare total_credits int;
> > declare total_balance int;
> >
> > select SUM(debits.amount) into total_debits from debits where
> > debits.enabled=1 and account=new.account;
> > select SUM(credits.amount) into total_credits from credits where
> > credits.enabled=1 and account=new.account;
> >
> > set total_balance = total_credits - total_debits;
> >
> > update accounts set accounts.balance = total_balance where
> > number=new.account;
> > end
> > //
> > DELIMITER ;
> >
> > CREATE TABLE `products` (
> > `id` int(11) NOT NULL auto_increment,
> > `description` text collate utf8_bin NOT NULL,
> > `daily_charge` int(11) NOT NULL default '0',
> > PRIMARY KEY (`id`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;
> >
> >
> > Thank you in advance.
> >
> > Dan
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daniel.saul@gmail.com
>
>

--00504502d189e992f00476262f78--