10 replies to this topic

[dprimedx]

I'm working on a billing & records system for a local NPO and I'm having some trouble with the database. I have a good deal of experience with MySQL and SQL Server 2005/2008 databases, but I just can't seem to get rid of this error 1054.

I've spent some time on Google and most of the results seem to be talking about this occurring in phpMyAdmin or some other piece of web based software, however, I'm using the MySQL prompt on my Linux box.

I have a trigger set up on the 'billing' table that should calculate the total fee based on the amount of time spent (used to calculate units) and the price per unit (stored in another table). Its my first time writing up a trigger, so I may have done something wrong with it.

The problem occurs when I insert a row into 'billing'. It gives me an error 1054 saying "Unknown column 'units' in 'field list'".

As there are a few related files, I've uploaded them to one of my sites and I will link to them near the end of the post.

The insert query I use is:

INSERT INTO billing (idc,EI,school,funding_source,serviceDate,totalTime,therapistID,mileage,serviceTypeID) values(0,'M','BAHS','BA','10-29-2008',27,0,0,0);

The files are all located in the following directory, if you'd rather me copy/paste them here, I have no issue with doing that. (I'd link to it, but I have < 10 posts)
davidrware.com/code/sql/

Thanks for the help and for taking the time to look this over for me. If you need any more information, just let me know.

[BlaineSch]

What is the exact error?

Try putting the column names in the `(backtick) that usually helps me.

[dprimedx]

The error:
Error 1054 Unknown column 'units' in 'field list'
It occurs whenever I try an insert on 'billing'.

I tried the insert with the backticks around the column names but I'm still receiving the same error.

[amrosama]

Quote

INSERT INTO billing (idc,EI,school,funding_source,serviceDate,totalTime,therapistID,mileage,serviceTypeID) values(0,'M','BAHS','BA','10-29-2008',27,0,0,0);

i cant find "units" field here, can you post the query you are trying to run?

[dprimedx]

amrosama said:

i cant find "units" field here, can you post the query you are trying to run?

The insert query is the one I'm trying to run. It shouldn't be adding in a value for units. The value for units should be calculated by the trigger.

[WingedPanther]

What's the trigger?

[dprimedx]

WingedPanther said:

What's the trigger?

USE care1;


DELIMITER $$

CREATE TRIGGER billing_units

	 BEFORE INSERT ON billing

	 FOR EACH ROW

BEGIN

	DECLARE tt INTEGER;

	DECLARE tmod DECIMAL(5,2);

	DECLARE price DECIMAL(5,2);


	SELECT totalTime INTO tt FROM billing WHERE idb = NEW.idb;

	SET tmod = MOD(tt,15);

	IF tmod = 0 THEN

		SET NEW.units = tmod/15;

	ELSE

		SET NEW.units = (tt+(15-MOD(tt,15)))/15;

	END IF;

	

	SELECT cpu INTO price FROM services WHERE services.ids = NEW.serviceTypeID;


	SET NEW.totalFee = units*price;

END$$

[WingedPanther]

I think that may be your problem. units isn't a field in NEW (based on the insert statement), so it fails when you try to access NEW.units.

[dprimedx]

USE care1;

DELIMITER $$
CREATE TRIGGER billing_units
	 BEFORE INSERT ON billing
	 FOR EACH ROW
BEGIN
	DECLARE tt INTEGER;
	DECLARE tmod DECIMAL(5,2);
	DECLARE price DECIMAL(5,2);

	SELECT totalTime INTO tt FROM billing WHERE idb = NEW.idb;
	SET tmod = MOD(tt,15);
	IF tmod = 0 THEN
		-- SET NEW.units = tmod/15;
		UPDATE billing SET units = tmod/15 WHERE idb = NEW.idb;
	ELSE
		-- SET NEW.units = (tt+(15-MOD(tt,15)))/15;
		UPDATE billing SET units = ((tt+15-MOD(tt,15)))/15 WHERE idb = NEW.idb;
	END IF;
	
	SELECT cpu INTO price FROM services WHERE services.ids = NEW.serviceTypeID;

	SET NEW.totalFee = units*price;
END$$

There's my new trigger. I no longer get the previous error, but now I'm getting Error 1442: Can't update 'billing' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any ideas on this new one? The insert statement is the same as above.

[WingedPanther]

Have you considered changing your trigger to AFTER INSERT so it can issue an update statement against the affected row?

[dprimedx]

WingedPanther said:

Have you considered changing your trigger to AFTER INSERT so it can issue an update statement against the affected row?

I change 'before' to 'after' as per the above recommendation. It gives me a new error now.

Error 1362 (HY0000) at line 4: Updating of NEW row is now allowed in after trigger

I'm thinking it might just be easier to do this in PHP instead of having the database do it. However, if someone can help me out with this part, I'll stick to SQL - it's been a good learning experience so far.