List:General Discussion« Previous MessageNext Message »
From:Dan Saul Date:October 17 2009 6:20pm
Subject:Error - "Select Column Not Found Within Trigger"
View as plain text  
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

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