Create Trigger in SQLite

related: SQLite

Example of creating trigger in SQLite, this is useful when updating a column automatically. This is an example to always fill the updated_at value with the current time when the data is being updated.

create table if not exists patients
(
    id               integer     not null primary key autoincrement,
    name             varchar(45) not null,
    microchip_number varchar(45),
    age              varchar(10),
    gender           varchar(10) not null,
    status           varchar(20) null,

    created_at       datetime default current_timestamp not null,
    updated_at       datetime    null,
    deleted_at       datetime    null
);

create trigger if not exists patients_trig
    after update
    on patients
begin
    update patients set updated_at = datetime('now') where id = NEW.id;
end;