简而言之，不能在触发器中使用CREATE TABLE。触发器仅限于执行BEGINEND部分/子句中的UPDATE、INSERT和SELECT操作(即可以采取/完成的操作)。SQLite理解的SQL-CREATE TRIGGER
我喜欢在用户注册创建帐户时动态创建一个表，所以我尝试过使用触发器，我不知道是否可以使用触发器动态创建表。我对安卓绝对是个新手。它显示语法错误。
我已经生成了一个数据库来输入我的数据，当多个帐户登录时，我喜欢为每个用户单独创建表，即单独的表，其中只包含数据，而不是用户名和密码。所以我创建了一个方法"trigger_table"，我在其中创建了触发器，并在"register"方法中调用了它，我在按钮b1中调用了register方法，其中b1是注册按钮。当我点击这个按钮时，应该自动创建一个表
public class sql{
public Boolean register(String username, String Password) {
SQLiteDatabase s = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("username", username);
values.put("Password", Password);
long row = s.insert("log", null, values);
trigger_table(username);//trigger method is called
if (row == -1)
return false;
else
return true;
}
}
public Boolean trigger_table(String t) {
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("CREATE TRIGGER aft_insert AFTER INSERT ON log AS BEGIN
CREATE TABLE"+t+"(username text ,Date date ,description text ,Amount
int)");
return true;// I have created method trigger table
}
b1.setOnClickListener((v) -> {
String s1 = t1.getText().toString();
String s2 = t2.getText().toString();
String s3 = t3.getText().toString();
try {
sql s = new sql(this);//this is class name which
Boolean bo ;
if(s2.equals(s3))
{
bo= s.register(s1,s2);//called register method
if(bo == true) {
b.setMessage("Account created")
.setPositiveButton("Ok",null);
AlertDialog al = b.create();
al.show();
}
}
else{
b.setMessage("Account does not created")
.setPositiveButton("Ok",null);
AlertDialog al = b.create();
al.show();
}
}
catch(Exception e) {
b.setMessage(e.toString())
.setPositiveButton("Ok", null);
AlertDialog al = b.create();
al.show();
t1.setText("");
t2.setText("");
}
});
其他重新评论：-
主要思想是当用户在注册时提供用户名和密码它存储在一个名为"日志"的表中，当数据如果插入了username，则必须为该用户创建一个新表。是有什么可行的方法吗？
这不能直接使用SQL来完成，有很多方法可以通过程序来完成。然而，这在很大程度上违背了典型/推荐的使用，因为当与当前用户相关的单个列足以用于其他数据的单个表时，具有相同模式的多个表具有许多缺点。
每个表至少有4k的开销，仅用于特定于该表的页面。模式中有更多的表(sqlite_master表)，因此在访问任何标识符(名称)时增加了处理需求，例如。
示例
下面是一个简单的例子，说明如何做到以上几点：-
数据库帮助程序(DBHelper.java)
：-
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "myuserdb";
public static final int DBVERSION = 1;
public static final String TBL_LOG = "log";
public static final String COL_LOG_ID = BaseColumns._ID;
public static final String COL_LOG_USERNAME = "user_name";
public static final String COL_LOG_PASSWORD = "password";
public static final String COL_USER_ID = BaseColumns._ID;
public static final String COL_USER_DATA = "user_data";
SQLiteDatabase mDB;
public DBHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String crt_log_table = "CREATE TABLE IF NOT EXISTS " + TBL_LOG + "(" +
COL_LOG_ID + " INTEGER PRIMARY KEY, " +
COL_LOG_USERNAME + " TEXT UNIQUE, " +
COL_LOG_PASSWORD + " TEXT" +
")";
db.execSQL(crt_log_table);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public long addLog(String user, String password) {
ContentValues cv = new ContentValues();
cv.put(COL_LOG_USERNAME,user);
cv.put(COL_LOG_PASSWORD,password);
long userid = mDB.insert(TBL_LOG,null,cv);
if (userid > 0) {
createUserSpecificTable(user);
}
return userid;
}
public boolean login(String user, String password) {
boolean rv = false;
String whereclause = COL_LOG_USERNAME + "=? AND " + COL_LOG_PASSWORD + "=?";
String[] whereargs = new String[]{user,password};
Cursor csr = mDB.query(TBL_LOG,null,whereclause,whereargs,null,null,null);
if (csr.getCount() > 0) {
rv = true;
}
csr.close();
return rv;
}
private void createUserSpecificTable(String user) {
String crt_userSpecific_table = "CREATE TABLE IF NOT EXISTS " + user + "(" +
COL_USER_ID + " INTEGER PRIARY KEY," +
COL_USER_DATA + " TEXT " +
")";
mDB.execSQL(crt_userSpecific_table);
}
public void addUserData(String user, String data) {
ContentValues cv = new ContentValues();
cv.put(COL_USER_DATA,data);
// User specific
mDB.insert(user,null,cv);
}
public Cursor getUserData(String user) {
return mDB.query(user,null,null,null,null,null,null);
}
}
- 方法addLog注册一个新用户并构建特定于用户的表
- 方法login允许用户登录，如果登录成功则返回true，否则返回false
- 方法adduserData根据传递的用户添加一些特定于用户的数据
- 方法getUserData将用户特定的数据作为Cursor返回
- 注意以上要求用户名遵守未封闭(标识符)表命名规则
测试活动
：-
以下内容创建了大量登录，模拟了一些用户会话，将用户的数据写入日志，然后将模式(sqlite_master的内容写入日志，以显示用户特定的表已经创建)：-
public class MainActivity extends AppCompatActivity {
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DBHelper(this);
mDBHlpr.addLog("Fred","1234");
mDBHlpr.addLog("Sue","5678");
mDBHlpr.addLog("Mary","1111");
mDBHlpr.addLog("Tom","0000");
doMockUserSession("MRNOBODY","HACKIT");
doMockUserSession("Fred","1234");
doMockUserSession("Sue","666");
doMockUserSession("Sue","5678");
doMockUserSession("Mary","1111");
doMockUserSession("Tom","0000");
//Get all Items from the schema and write to the log
DatabaseUtils.dumpCursor(mDBHlpr.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null));
}
private void doMockUserSession(String user, String password) {
if (mDBHlpr.login(user,password)) {
mDBHlpr.addUserData(user,"MY DATA " + String.valueOf(System.currentTimeMillis()));
Cursor csr = mDBHlpr.getUserData(user);
DatabaseUtils.dumpCursor(csr);
csr.close();
} else {
Log.d("LOGINFAIL","Login failed for user " + user);
}
}
}
结果输出
以上(几次运行后)导致：-
2019-02-13 10:07:06.170 D/LOGINFAIL: Login failed for user MRNOBODY
2019-02-13 10:07:06.173 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5690395
2019-02-13 10:07:06.175 I/System.out: 0 {
2019-02-13 10:07:06.175 I/System.out: _id=null
2019-02-13 10:07:06.175 I/System.out: user_data=MY DATA 1550011948580
2019-02-13 10:07:06.175 I/System.out: }
2019-02-13 10:07:06.175 I/System.out: 1 {
2019-02-13 10:07:06.176 I/System.out: _id=null
2019-02-13 10:07:06.176 I/System.out: user_data=MY DATA 1550012073536
2019-02-13 10:07:06.176 I/System.out: }
2019-02-13 10:07:06.176 I/System.out: 2 {
2019-02-13 10:07:06.176 I/System.out: _id=null
2019-02-13 10:07:06.176 I/System.out: user_data=MY DATA 1550012826172
2019-02-13 10:07:06.176 I/System.out: }
2019-02-13 10:07:06.176 I/System.out: <<<<<
2019-02-13 10:07:06.178 D/LOGINFAIL: Login failed for user Sue
2019-02-13 10:07:06.179 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@70c1caa
2019-02-13 10:07:06.179 I/System.out: 0 {
2019-02-13 10:07:06.179 I/System.out: _id=null
2019-02-13 10:07:06.179 I/System.out: user_data=MY DATA 1550011948588
2019-02-13 10:07:06.179 I/System.out: }
2019-02-13 10:07:06.179 I/System.out: 1 {
2019-02-13 10:07:06.179 I/System.out: _id=null
2019-02-13 10:07:06.180 I/System.out: user_data=MY DATA 1550012073545
2019-02-13 10:07:06.180 I/System.out: }
2019-02-13 10:07:06.180 I/System.out: 2 {
2019-02-13 10:07:06.180 I/System.out: _id=null
2019-02-13 10:07:06.180 I/System.out: user_data=MY DATA 1550012826178
2019-02-13 10:07:06.181 I/System.out: }
2019-02-13 10:07:06.181 I/System.out: <<<<<
2019-02-13 10:07:06.182 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@660309b
2019-02-13 10:07:06.182 I/System.out: 0 {
2019-02-13 10:07:06.182 I/System.out: _id=null
2019-02-13 10:07:06.182 I/System.out: user_data=MY DATA 1550011948594
2019-02-13 10:07:06.182 I/System.out: }
2019-02-13 10:07:06.182 I/System.out: 1 {
2019-02-13 10:07:06.183 I/System.out: _id=null
2019-02-13 10:07:06.183 I/System.out: user_data=MY DATA 1550012073547
2019-02-13 10:07:06.183 I/System.out: }
2019-02-13 10:07:06.183 I/System.out: 2 {
2019-02-13 10:07:06.183 I/System.out: _id=null
2019-02-13 10:07:06.183 I/System.out: user_data=MY DATA 1550012826181
2019-02-13 10:07:06.183 I/System.out: }
2019-02-13 10:07:06.183 I/System.out: <<<<<
2019-02-13 10:07:06.186 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8a61c38
2019-02-13 10:07:06.186 I/System.out: 0 {
2019-02-13 10:07:06.186 I/System.out: _id=null
2019-02-13 10:07:06.186 I/System.out: user_data=MY DATA 1550011948596
2019-02-13 10:07:06.186 I/System.out: }
2019-02-13 10:07:06.186 I/System.out: 1 {
2019-02-13 10:07:06.186 I/System.out: _id=null
2019-02-13 10:07:06.186 I/System.out: user_data=MY DATA 1550012073550
2019-02-13 10:07:06.186 I/System.out: }
2019-02-13 10:07:06.187 I/System.out: 2 {
2019-02-13 10:07:06.187 I/System.out: _id=null
2019-02-13 10:07:06.187 I/System.out: user_data=MY DATA 1550012826185
2019-02-13 10:07:06.187 I/System.out: }
2019-02-13 10:07:06.187 I/System.out: <<<<<
2019-02-13 10:07:06.187 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@6a34411
2019-02-13 10:07:06.187 I/System.out: 0 {
2019-02-13 10:07:06.187 I/System.out: type=table
2019-02-13 10:07:06.187 I/System.out: name=android_metadata
2019-02-13 10:07:06.188 I/System.out: tbl_name=android_metadata
2019-02-13 10:07:06.188 I/System.out: rootpage=3
2019-02-13 10:07:06.188 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2019-02-13 10:07:06.188 I/System.out: }
2019-02-13 10:07:06.188 I/System.out: 1 {
2019-02-13 10:07:06.188 I/System.out: type=table
2019-02-13 10:07:06.188 I/System.out: name=log
2019-02-13 10:07:06.188 I/System.out: tbl_name=log
2019-02-13 10:07:06.188 I/System.out: rootpage=4
2019-02-13 10:07:06.188 I/System.out: sql=CREATE TABLE log(_id INTEGER PRIMARY KEY, user_name TEXT UNIQUE, password TEXT)
2019-02-13 10:07:06.188 I/System.out: }
2019-02-13 10:07:06.188 I/System.out: 2 {
2019-02-13 10:07:06.188 I/System.out: type=index
2019-02-13 10:07:06.188 I/System.out: name=sqlite_autoindex_log_1
2019-02-13 10:07:06.188 I/System.out: tbl_name=log
2019-02-13 10:07:06.188 I/System.out: rootpage=5
2019-02-13 10:07:06.188 I/System.out: sql=null
2019-02-13 10:07:06.188 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 3 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.189 I/System.out: name=Fred
2019-02-13 10:07:06.189 I/System.out: tbl_name=Fred
2019-02-13 10:07:06.189 I/System.out: rootpage=6
2019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Fred(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.189 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 4 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.189 I/System.out: name=Sue
2019-02-13 10:07:06.189 I/System.out: tbl_name=Sue
2019-02-13 10:07:06.189 I/System.out: rootpage=7
2019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Sue(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.189 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 5 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.189 I/System.out: name=Mary
2019-02-13 10:07:06.189 I/System.out: tbl_name=Mary
2019-02-13 10:07:06.189 I/System.out: rootpage=8
2019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Mary(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.189 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 6 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.190 I/System.out: name=Tom
2019-02-13 10:07:06.190 I/System.out: tbl_name=Tom
2019-02-13 10:07:06.190 I/System.out: rootpage=9
2019-02-13 10:07:06.190 I/System.out: sql=CREATE TABLE Tom(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.190 I/System.out: }
2019-02-13 10:07:06.190 I/System.out: <<<<<