List:General Discussion« Previous MessageNext Message »
From:Dan Saul Date:October 17 2009 7:04pm
Subject:Re: Error - "Select Column Not Found Within Trigger"
View as plain text  
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 <mdykman@stripped> 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 <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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>

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