List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:October 17 2009 6:25pm
Subject:Re: Error - "Select Column Not Found Within Trigger"
View as plain text  
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
Thread
Error - "Select Column Not Found Within Trigger"Dan Saul17 Oct
Re: Error - "Select Column Not Found Within Trigger"Michael Dykman17 Oct
  • Re: Error - "Select Column Not Found Within Trigger"Dan Saul17 Oct