void TemplatesDBManager::createTriggers() {
std::string insertTrigger = "CREATE TRIGGER IF NOT EXISTS on_template_insert AFTER INSERT ON Templates BEGIN UPDATE Templates SET number = (SELECT max(number) + 1 FROM Templates) WHERE number = -1; END;";
std::string deleteTrigger = "CREATE TRIGGER IF NOT EXISTS on_template_delete AFTER DELETE ON Templates BEGIN UPDATE Templates SET number = number - 1 WHERE number > OLD.number; END;";
executeQueryWithoutResult(insertTrigger);
executeQueryWithoutResult(deleteTrigger);
}
//组合delete语句
int deleteData(base *base, char *table) {
MYSQL *conn = initMysql();
char *delete = "DELETE FROM "; //`user` WHERE `id`='7'";
char field_where[1024] = { 0 };
char sql[2048] = { 0 };
compositeDeteleSql(base, field_where);
sprintf(sql, "%s `%s` where %s", delete, table, field_where);
fprintf(cgiOut, "%s", sql);
executeQueryWithoutResult(sql);
return 1;
}
//组合更新语句
int updateData(base *base, char *table, int field_cnt, int where_cnt) {
MYSQL *conn = initMysql();
char *update = "UPDATE "; // `test`.`user` SET `pwd`='11' WHERE `id`='1';
char field_select[1024] = { 0 };
char field_where[1024] = { 0 };
char sql[1024] = { 0 };
compositeUpdateSql(base, field_select, field_where, field_cnt, where_cnt);
sprintf(sql, "%s `%s` SET %s WHERE %s", update, table, field_select,
field_where);
fprintf(cgiOut, "%s", sql);
executeQueryWithoutResult(sql);
return 1;
}
//组合Insert语句
int insertData(base *parent, char *table) {
MYSQL *conn = initMysql();
char field_name_sql[1048] = { 0 };
char field_value_sql[1048] = { 0 };
int field_name_length = 0;
int field_value_length = 0;
char *insert = "INSERT INTO";
char sql[2048] = { 0 };
compositeInsertSql(parent, field_name_sql, field_value_sql);
sprintf(sql, "%s `%s` %s VALUES %s; ", insert, table, field_name_sql,
field_value_sql);
executeQueryWithoutResult(sql);
int last_id = mysql_insert_id(conn);
fprintf(cgiOut, "%s", sql);
return last_id;
}
