Probleme mit Insert in eine Tabelle

Oliver_d05971 · 10. November 2019 um 00:14

Hallo an alle!
hier ist mein Problem besser erklärt, also ich habe eine Tabelle (question_item) erstellt. Mit dieser Tabelle habe ich 2 Trigger (increment_count_QUESTION_ITEM)und(decrement_count_QUESTION_ITEM erstellt. Mein Problem ist: wenn ich irgendwas in meine Tabelle mit INSERT einfügen will, bekomme ich so eine Meldung:
ERROR 1054 (42S22) : Unkown colomn ‚count_question_item‘ in ’ field list’
Wie kann ich diesen Fehler aufheben?
vielen vielen Dank im Voraus…
Oli.

hier ist die Tabelle mit dem Trigger:

CREATE TABLE QUESTION_ITEM(questionItem_ID INT(6),informationItem_ID INT(6), isLeaf INT(1), itemText varchar(255), endUser_ID INT(4), photoFileName varchar(128), userInterested INT(1), primary key (questionItem_ID) );

create table counter_QUESTION_ITEM (questionItem_ID INT(6) primary key, count_questionItem_ID int,
informationItem_ID INT(6) , count_informationItem_ID int,
isLeaf INT(1), count_isLeaf int,
itemText varchar(255), count_itemText int,
endUser_ID INT(4) , count_endUser_ID int,
photoFileName varchar(255), count_photoFileName int,
userInterested varchar(255), count_userInterested int);

DELIMITER //

create trigger increment_count_QUESTION_ITEM after INSERT on QUESTION_ITEM for each row
begin
insert into counter_QUESTION_ITEM (questionItem_ID, count_QUESTION_ITEM) values (NEW.questionItem_ID, 1) on duplicate key update count_questionItem_ID = count_questionItem_ID + 1;

insert into counter_QUESTION_ITEM (informationItem_ID, count_informationItem_ID) values (NEW.informationItem_ID, 1) on duplicate key update count_informationItem_ID = count_informationItem_ID + 1;

insert into counter_QUESTION_ITEM (isLeaf, count_isLeaf) values (NEW.isLeaf, 1) on duplicate key update count_isLeaf = count_isLeaf + 1;

insert into counter_QUESTION_ITEM (itemText, count_itemText) values (NEW.itemText, 1) on duplicate key update count_itemText = count_itemText + 1;

insert into counter_QUESTION_ITEM (endUser_ID, count_endUser_ID) values (NEW.endUser_ID, 1) on duplicate key update count_endUser_ID = count_endUser_ID + 1;

insert into counter_QUESTION_ITEM (photoFileName, count_photoFileName) values (NEW.photoFileName, 1) on duplicate key update count_photoFileName = count_photoFileName + 1;

insert into counter_QUESTION_ITEM (userInterested, count_userInterested) values (NEW.userInterested, 1) on duplicate key update count_userInterested = count_userInterested + 1;

end //

create trigger decrement_count_QUESTION_ITEM after DELETE on QUESTION_ITEM for each row
begin
update counter_QUESTION_ITEM SET count_questionItem_ID = count_questionItem_ID - 1 WHERE questionItem_ID = OLD.questionItem_ID;

update counter_QUESTION_ITEM SET count_informationItem_ID = count_informationItem_ID - 1 WHERE informationItem_ID = OLD.informationItem_ID;

update counter_QUESTION_ITEM SET count_isLeaf = count_isLeaf - 1 WHERE isLeaf = OLD.isLeaf;

update counter_QUESTION_ITEM SET count_itemText = count_itemText - 1 WHERE itemText = OLD.itemText;

update counter_QUESTION_ITEM SET count_endUser_ID = count_endUser_ID - 1 WHERE endUser_ID = OLD.endUser_ID;

update counter_QUESTION_ITEM SET count_photoFileName = count_photoFileName - 1 WHERE photoFileName = OLD.photoFileName;

update counter_QUESTION_ITEM SET count_userInterested = count_userInterested - 1 WHERE userInterested = OLD.userInterested;
end //

DELIMITER //

Ulrich_Vaid · 10. November 2019 um 00:15

Das Feld „count_QUESTION_ITEM“ exisitert in der Tabelle „counter_QUESTION_ITEM“ nicht, wie dir die Fehlermeldung ja auch sagt…Dein Insert Trigger schlägt also fehl

Gruss

Hallo an alle!
hier ist mein Problem besser erklärt, also ich habe eine
Tabelle (question_item) erstellt. Mit dieser Tabelle habe ich
2 Trigger
(increment_count_QUESTION_ITEM)und(decrement_count_QUESTION_ITEM
erstellt. Mein Problem ist: wenn ich irgendwas in meine
Tabelle mit INSERT einfügen will, bekomme ich so eine Meldung:
ERROR 1054 (42S22) : Unkown colomn ‚count_question_item‘ in ’
field list’
Wie kann ich diesen Fehler aufheben?
vielen vielen Dank im Voraus…
Oli.

hier ist die Tabelle mit dem Trigger:

CREATE TABLE QUESTION_ITEM(questionItem_ID
INT(6),informationItem_ID INT(6), isLeaf INT(1), itemText
varchar(255), endUser_ID INT(4), photoFileName varchar(128),
userInterested INT(1), primary key (questionItem_ID) );

create table counter_QUESTION_ITEM (questionItem_ID INT(6)
primary key, count_questionItem_ID int,
informationItem_ID INT(6) ,
count_informationItem_ID int,
isLeaf INT(1), count_isLeaf int,
itemText varchar(255), count_itemText
int,
endUser_ID INT(4) , count_endUser_ID
int,
photoFileName varchar(255),
count_photoFileName int,
userInterested varchar(255),
count_userInterested int);

DELIMITER //

create trigger increment_count_QUESTION_ITEM after INSERT on
QUESTION_ITEM for each row
begin
insert into counter_QUESTION_ITEM (questionItem_ID,
count_QUESTION_ITEM) values (NEW.questionItem_ID, 1) on
duplicate key update count_questionItem_ID =
count_questionItem_ID + 1;

Oliver_d05971 · 10. November 2019 um 00:15

Hallo,
vielen dank für deinen nutzlichen Hinweis, ich habe das korregiert aber jetzt bekomme ich eine andere Fehlermeldung:
Error 1364 (HY000): Field ‚questionItem_ID‘ does not have a default value

wisst ihr vielleicht was das bedeutet?
ich wäre echt sehr dankbar
Oli.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Ulrich_Vaid · 10. November 2019 um 00:15

Hallo,
vielen dank für deinen nutzlichen Hinweis, ich habe das
korregiert aber jetzt bekomme ich eine andere Fehlermeldung:
Error 1364 (HY000): Field ‚questionItem_ID‘ does not have a
default value

Bei welcher Aktion kommt diese Meldung ?

Oliver_d05971 · 10. November 2019 um 00:16

Diese Meldung (Error 1364 (HY000): Field ‚questionItem_ID‘ does not have a default value)
kommt wenn ich irgendwas in meine Tabelle „question_item“ mit INSERT einfüge… also z.B:
INSERT INTO question_item (questionItem_ID) VALUES (8);

Hier ist der Code von der Tabelle und dem Trigger nochmal:

CREATE TABLE QUESTION_ITEM(questionItem_ID INT(6),informationItem_ID INT(6), isLeaf INT(1), itemText varchar(255), endUser_ID INT(4), photoFileName varchar(128), userInterested INT(1), primary key (questionItem_ID) );

create table counter_QUESTION_ITEM (questionItem_ID INT(6) primary key, count_questionItem_ID int,
informationItem_ID INT(6) , count_informationItem_ID int,
isLeaf INT(1), count_isLeaf int,
itemText varchar(255), count_itemText int,
endUser_ID INT(4) , count_endUser_ID int,
photoFileName varchar(255), count_photoFileName int,
userInterested varchar(255), count_userInterested int);

DELIMITER //

create trigger increment_count_QUESTION_ITEM after INSERT on QUESTION_ITEM for each row
begin
insert into counter_QUESTION_ITEM (questionItem_ID, count_questionItem_ID) values (NEW.questionItem_ID, 1) on duplicate key update count_questionItem_ID = count_questionItem_ID + 1;

insert into counter_QUESTION_ITEM (informationItem_ID, count_informationItem_ID) values (NEW.informationItem_ID, 1) on duplicate key update count_informationItem_ID = count_informationItem_ID + 1;

insert into counter_QUESTION_ITEM (isLeaf, count_isLeaf) values (NEW.isLeaf, 1) on duplicate key update count_isLeaf = count_isLeaf + 1;

insert into counter_QUESTION_ITEM (itemText, count_itemText) values (NEW.itemText, 1) on duplicate key update count_itemText = count_itemText + 1;

insert into counter_QUESTION_ITEM (endUser_ID, count_endUser_ID) values (NEW.endUser_ID, 1) on duplicate key update count_endUser_ID = count_endUser_ID + 1;

insert into counter_QUESTION_ITEM (photoFileName, count_photoFileName) values (NEW.photoFileName, 1) on duplicate key update count_photoFileName = count_photoFileName + 1;

insert into counter_QUESTION_ITEM (userInterested, count_userInterested) values (NEW.userInterested, 1) on duplicate key update count_userInterested = count_userInterested + 1;

end //

create trigger decrement_count_QUESTION_ITEM after DELETE on QUESTION_ITEM for each row
begin
update counter_QUESTION_ITEM SET count_questionItem_ID = count_questionItem_ID - 1 WHERE questionItem_ID = OLD.questionItem_ID;

update counter_QUESTION_ITEM SET count_informationItem_ID = count_informationItem_ID - 1 WHERE informationItem_ID = OLD.informationItem_ID;

update counter_QUESTION_ITEM SET count_isLeaf = count_isLeaf - 1 WHERE isLeaf = OLD.isLeaf;

update counter_QUESTION_ITEM SET count_itemText = count_itemText - 1 WHERE itemText = OLD.itemText;

update counter_QUESTION_ITEM SET count_endUser_ID = count_endUser_ID - 1 WHERE endUser_ID = OLD.endUser_ID;

update counter_QUESTION_ITEM SET count_photoFileName = count_photoFileName - 1 WHERE photoFileName = OLD.photoFileName;

update counter_QUESTION_ITEM SET count_userInterested = count_userInterested - 1 WHERE userInterested = OLD.userInterested;
end //

DELIMITER //

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]