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 <mdyk...@gmail.com> 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.s...@gmail.com> 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 > - mdyk...@gmail.com > > "May you live every day of your life." > Jonathan Swift > > > > -- > - michael dykman > - mdyk...@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.s...@gmail.com > >
