inserting a new row in a table

[former_SQLA_member1695070]

I'm new to SQL Anywhere. I have a database that was not designed by me. I'm trying to insert a new row in a table with let's say 3 columns. When I execute the sql statement insert into table (Col, Col2, Col3) values('1','2','3') I get a message: Column 'ColX' not found Error code=-143, SQL state=42S22 There is no such ColX. I tried to search in the triggers, but they are too many. With other tables I do not have that problem. Could anyone please help me?

[fvestjens]

Try select traceback() directly after the error. This will give you more information on the error. In which script it occurs and the line number in the script.

[former_SQLA_member1694908]

Hi, Generate a full database script (extract structure) to a file and search the keyword

[former_SQLA_member1695070]

Thank's a lot both of you! It's definitely the triggers and procedures called from them. There are some temporary tables. Obviously the application that uses the database creates them, and then performs inserts. For the time being

SET TEMPORARY OPTION FIRE_TRIGGERS = OFF

suits me fine, although there are some side effects. I intend to do a bulk insert.

[VolkerBarth]

If you want to do a bulk insert, you may also use the LOAD TABLE statement, as the docs state:

The LOAD TABLE statement does not fire any triggers associated with the table.

And you can additionaly specify whether COMUPUTED columns should be re-caluculated and DEFAULTS should be regarded.

(Of course we can't tell whether ignoring these may lead to desired or undesired side effects.)

[former_SQLA_member1694901]

I assume that you have tried select * from table; in an ISQL to verify that the column name exists...

If there are triggers on the table, this may also be an error in a trigger that has nothing to do with your statement. The trigger might even try to insert in a completely different table.

Or "table" may be a view, and the problematic column is a calculated value.

So, in principle I agree @SamuelCosta and these are just some things that are easily overlooked in such an analysis. 😉

[former_SQLA_member1694947]

Well, it may be happen because of triggers, you can disable trigger and then try again adding new row. You can disable trigger in below way.

USE mydatabase;

ALTER TABLE mytable DISABLE TRIGGER ALL;

INSERT INTO mytable (Col, Col2, Col3) VALUES('1','2','3');

ALTER TABLE mytable ENABLE TRIGGER ALL;

Thanks

[chris_keating]

This syntax is not supported by SQL Anywhere.