PureBasic Forums - English

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,GetCurrentDirectory()+"mydata.sqlite")
  CloseFile(0)
EndIf

db=OpenDatabase(#PB_Any,GetCurrentDirectory()+"mydata.sqlite", "", "",#PB_Database_SQLite)
If db
  DatabaseUpdate(db,"CREATE TABLE settings(timestamp varchar(10));")
  DatabaseUpdate(db,"Insert into settings (timestamp)values('2023-07-19');")
  
  DatabaseQuery(db,"Select timestamp from settings;")
  If NextDatabaseRow(db)
    Debug GetDatabaseString(db,0)
  EndIf
  
  DatabaseUpdate(db,"Update settings set timestamp='2023-05-22';")
   
  DatabaseQuery(db,"Select timestamp from settings;")
  If NextDatabaseRow(db)
    Debug GetDatabaseString(db,0)
  EndIf
  
  CloseDatabase(db)
EndIf

Code: Select all

CREATE TABLE ChangeTimestamps
(
KeyID INTEGER PRIMARY KEY NOT NULL,
KeyName TEXT NOT NULL UNIQUE,
KeyTimestamp TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO ChangeTimestamps(KeyName) 
VALUES ('ObservedTableNameInsert'),
('ObservedTableNameDelete'), 
('ObservedTableNameUpdate');

CREATE TRIGGER ObservedTableInsertTimestamp
AFTER INSERT ON ObservedTableName
BEGIN
  UPDATE ChangeTimestamps
  SET KeyTimestamp = CURRENT_TIMESTAMP
  WHERE KeyName = 'ObservedTableNameInsert';
END;

CREATE TRIGGER ObservedTableDeleteTimestamp
AFTER DELETE ON ObservedTableName
BEGIN
  UPDATE ChangeTimestamps
  SET KeyTimestamp = CURRENT_TIMESTAMP
  WHERE KeyName = 'ObservedTableNameDelete';
END;

CREATE TRIGGER ObservedTableUpdateTimestamp
AFTER UPDATE ON ObservedTableName
BEGIN
  UPDATE ChangeTimestamps
  SET KeyTimestamp = CURRENT_TIMESTAMP
  WHERE KeyName = 'ObservedTableNameUpdate';
END;