DML Triggers

  static public class GenericBackupTriggerExecutor extends TriggerExecutor {

    public final String backupTableName;
    public final String tsColumnName;
    public final TriggerUtils triggerUtils;

    public GenericBackupTriggerExecutor(TriggerUtils triggerUtils, TypedNamedValuesI newValues, TypedNamedValuesI oldValues, String backupTableName, String tsColumnName) {
      super(newValues, oldValues);
      this.backupTableName = backupTableName;
      this.tsColumnName = tsColumnName;
      this.triggerUtils = triggerUtils;
    }

    @Override
    public String getExecutorName() {
      return triggerName;
    }

    @Override
    public void execute() {
      if (!hasValues()) { // delete
        return;
      }
      try (Session session = triggerUtils.session()) {
        Table table = session.database().getTable(backupTableName);
        Tuple tuple = table.createTuple();
        for (String column : valuesFieldNames()) {
          tuple.put(column, getValue(column));
        }
        tuple.put(tsColumnName, Timestamp.valueOf(LocalDateTime.now()));
        table.insert(tuple);
        // no need to commit when sharing QE's transaction
      } catch (Exception exception) {
        throw new LeanxcaleRuntimeException(exception);
      }
    }
  }

  /**
   * Create a TypedNamesValues
   * @param positionsMap Map value position by name
   * @param row values
   * @param typeMap Map SQL type by name
   */
  public TypedNamedValuesImpl(Map<String, Integer> positionsMap, Object[] row, Map<String, ExpressionType> typeMap) {
    ...
  }

  /**
   * Create a NamedValues object
   * @param names values names
   * @param row values ordered by names
   * @param types  SQL types ordered by names
   */
  public TypedNamedValuesImpl(String[] names, Object[] row, int[] types) {
    ...
  }

create table tauto (i int AS IDENTITY start with 10 increment by 10, v VARCHAR, PRIMARY KEY (i));

create table tauto (i int, v VARCHAR, PRIMARY KEY (i));

create sequence tauto_i_seq start with 10 increment by 10;

CREATE TRIGGER tauto_i_seq_trigger BEFORE INSERT ON tauto
FOR EACH ROW EXECUTE autoIncrementTrigger('i,tauto_i_seq')
PRIORITY 100;

-- Note that the autoincrement colums will become an implicit column for inserts
insert into tauto values ('value');

create table t (wkt VARCHAR, latitude double, longitude double, PRIMARY GEOHASH KEY (wkt), GEOHASH (latitude,longitude));

create table t (wkt VARCHAR, latitude double, longitude double, gh_wkt VARCHAR PRIMARY KEY, gh_lat_long VARCHAR);

CREATE TRIGGER gh_wkt_trigger BEFORE INSERT ON t
FOR EACH ROW EXECUTE AutoGeohashTrigger('gh_wkt,wkt')
PRIORITY 100;

CREATE TRIGGER gh_lat_long_trigger BEFORE INSERT ON t
FOR EACH ROW EXECUTE AutoGeohashTrigger('gh_lat_long,latitude,longitude')
PRIORITY 100;

-- Note that GEOHASH clause will create and index too, so we create it here manually
CREATE INDEX gh_lat_long_index ON t (gh_lat_long);

-- Note that the geohash column will become an implicit column for inserts and selects
insert into t values (ST_asText(ST_MakePoint(3.41,40.25)),cast(40.25 as double), cast(3.41 as double));

create table tauto (v VARCHAR);

create table t (id VARCHAR, v VARCHAR, PRIMARY KEY (id));

CREATE TRIGGER uuid_trigger BEFORE INSERT ON t
FOR EACH ROW EXECUTE UUIDTrigger('id')
PRIORITY 100;

-- Note that the uuid column will become a implicit column for inserts and selects
insert into t values ('value');

> create table tpk (i int, v VARCHAR, i2 int, PRIMARY KEY (i));

> create table tfk (pk int, v VARCHAR, fk int, PRIMARY KEY (pk), CONSTRAINT fKi2 FOREIGN KEY (fk) REFERENCES tpk(i));

> ENABLE TRIGGER fKi2 ON tfk;

> ENABLE TRIGGER fKi2 ON tpk;

> select * from LXSYSMETA.TRIGGERS;
triggerName      FKI2
triggerFunction  DeleteForeignKeyTrigger
args             {"fkTableName":"APP.TFK","columns":["I"],"fkColumns":["FK"]}
isBefore         true
isInsert         false
isUpdate         false
isDelete         true
priority         1
isEnable         true
tableCat         db
tableSchem       APP
tableName        TPK

triggerName      FKI2_NO_CONFLICT
triggerFunction  ForeignKeyTrigger
args             {"doCreateConflict":false,"refTableName":"APP.TPK","columns":["FK"],"refColumns":["I"],"parentTriggerName":"FKI2"}
isBefore         true
isInsert         true
isUpdate         true
isDelete         false
priority         1
isEnable         false
tableCat         db
tableSchem       APP
tableName        TFK

triggerName      FKI2
triggerFunction  ForeignKeyTrigger
args             {"doCreateConflict":true,"refTableName":"APP.TPK","columns":["FK"],"refColumns":["I"],"parentTriggerName":"FKI2"}
isBefore         true
isInsert         true
isUpdate         true
isDelete         false
priority         1
isEnable         true
tableCat         db
tableSchem       APP
tableName        TFK