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 <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 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@stripped
"May you live every day of your life."
Jonathan Swift
--
- michael dykman
- mdykman@stripped
"May you live every day of your life."
Jonathan Swift
