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
>
>
