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
