Editor simple de SQLite.

Son tan solo 200 líneas de código y desde ya, la librería de SQLite. Desde ya hay programas potentes y también portables como SQLite Studio, el cual uso y recomiendo. Volviendo al editor simple, lo hice para un uso muy específico y limitado, conecta a la base de datos que le indiquemos, muestra las tablas, permite ejecutar consultar cuyo resultado se muestra en un TDBGrid el cual cuenta con un par de opciones y ejecutar sentencias de actualizaciones que realiza mediante TZConnection.ExecuteDirect.
También tiene una opción para leer de un archivo un script SQL y luego ejecutarlo.
Creo que puede ser útil para quienes comiencen con SQLite, Lazarus y ZeosLib.

El código: descargar

unit principal;

{$mode objfpc}{$H+}

interface

uses
Classes, SysUtils, db, FileUtil, Forms, Controls, Graphics, Dialogs,
StdCtrls, Buttons, DBGrids, DbCtrls, ZConnection, ZDataset;

type
  TConexion=(Conectado, NoConectado);

type

{ TForm1 }

TForm1 = class(TForm)
  BBorrarMemo: TBitBtn;
  BCerrarConsulta: TBitBtn;
  btnSelBD: TBitBtn;
  btnConectar: TBitBtn;
  BCerrar: TBitBtn;
  btnDesconectar: TBitBtn;
  BExecute: TBitBtn;
  BConsulta: TBitBtn;
  btnLeerArchivo: TBitBtn;
  cbAutoCommit: TCheckBox;
  cbAutoSizeCol: TCheckBox;
  DataSource1: TDataSource;
  DBGrid1: TDBGrid;
  DBNavigator1: TDBNavigator;
  edBaseDeDatos: TEdit;
  edConexion: TEdit;
  Label1: TLabel;
  lbTablas: TListBox;
  Memo1: TMemo;
  OpenDialog1: TOpenDialog;
  ZConnection1: TZConnection;
  ZQuery1: TZQuery;
  procedure BBorrarMemoClick(Sender: TObject);
  procedure BCerrarConsultaClick(Sender: TObject);
  procedure BConsultaClick(Sender: TObject);
  procedure BExecuteClick(Sender: TObject);
  procedure btnLeerArchivoClick(Sender: TObject);
  procedure btnSelBDClick(Sender: TObject);
  procedure btnConectarClick(Sender: TObject);
  procedure BCerrarClick(Sender: TObject);
  procedure btnDesconectarClick(Sender: TObject);
  procedure cbAutoSizeColChange(Sender: TObject);
  procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  procedure FormCreate(Sender: TObject);
  procedure lbTablasDblClick(Sender: TObject);
private
  Conexion:TConexion;
  procedure MuestroTablas;
  function HayConexion:Boolean;
{ private declarations }
public
{ public declarations }
end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  Conexion:=NoConectado;
end;

procedure TForm1.btnSelBDClick(Sender: TObject);
begin
  if OpenDialog1.Execute then edBaseDeDatos.Text:= OpenDialog1.FileName;
end;

procedure TForm1.btnLeerArchivoClick(Sender: TObject);
begin
 if OpenDialog1.Execute then Memo1.Lines.LoadFromFile(OpenDialog1.FileName);
end;

procedure TForm1.BBorrarMemoClick(Sender: TObject);
begin
  Memo1.Clear;
end;

procedure TForm1.BCerrarConsultaClick(Sender: TObject);
begin
  ZQuery1.Close;
end;

procedure TForm1.BConsultaClick(Sender: TObject);
begin
  if not(HayConexion) then Exit;
  ZQuery1.Close;
  ZQuery1.SQL.Text:=Memo1.Text;
  ZQuery1.Open;
end;

procedure TForm1.BExecuteClick(Sender: TObject);
var
  n:Integer;
begin
  if not(HayConexion) then Exit;
  if ZConnection1.ExecuteDirect(Memo1.Text, n) then Memo1.Lines.Add('OK! '+IntToStr(n)+'      filas.');
end;

procedure TForm1.btnConectarClick(Sender: TObject);
begin
  if Conexion=Conectado then
  begin
    ShowMessage('Hay una conexión establecida, primero desconecte dicha conexión.');
    Exit;
  end;
  if not (FileExists(edBaseDeDatos.Text)) then exit;
  ZConnection1.Database:=edBaseDeDatos.Text;
  if not(cbAutoCommit.Checked) then ZConnection1.AutoCommit:=False;
  ZConnection1.Connect;
  if ZConnection1.Connected then
  begin
    edConexion.Text:='Conectado';
    edConexion.Font.Color:=clGreen;
    Conexion:=Conectado;
    MuestroTablas;
  end;
end;

procedure TForm1.BCerrarClick(Sender: TObject);
begin
  Close;
end;

procedure TForm1.btnDesconectarClick(Sender: TObject);
begin
  ZConnection1.Disconnect;
  edConexion.Text:='Desconectado';
  edConexion.Font.Color:=clRed;
  Conexion:=NoConectado;
  lbTablas.Clear;
end;

procedure TForm1.cbAutoSizeColChange(Sender: TObject);
begin
  if cbAutoSizeCol.Checked then DBGrid1.AutoFillColumns:=False else DBGrid1.AutoFillColumns:=True;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  ZConnection1.Disconnect;
  CloseAction:=caFree;
end;

procedure TForm1.lbTablasDblClick(Sender: TObject);
begin
  if not(HayConexion) then Exit;
  Memo1.Clear;
  Memo1.Lines.Add('SELECT * FROM '+lbTablas.Items[lbTablas.ItemIndex]+';');
  ZQuery1.Close;
  ZQuery1.SQL.Text:=Memo1.Text;
  ZQuery1.Open;
end;

procedure TForm1.MuestroTablas;
var
  ZQTablas:TZQuery;
begin
  lbTablas.Clear;
  ZQTablas:=TZQuery.Create(nil);
  ZQTablas.Connection:=ZConnection1;
  ZQTablas.SQL.Text:='SELECT name FROM sqlite_master WHERE type='+
  QuotedStr('table')+' and name <>'+QuotedStr('sqlite_sequence');
  ZQTablas.Open;
  if ZQTablas.RecordCount <1 then Exit;
  ZQTablas.First;
  while not(ZQTablas.EOF) do
  begin
    lbTablas.AddItem(ZQTablas.FieldByName('name').AsString,lbTablas);
    ZQTablas.Next;
  end;
  ZQTablas.Close;
  FreeAndNil(ZQTablas);
end;

function TForm1.HayConexion: Boolean;
begin
  if not(ZConnection1.Connected) then
  begin
    Memo1.Lines.Add('No hay establecida ninguna conexión.');
    Exit(False);
  end;
  Result:=True;
end;

end.

Formato de fecha y hora en SQLite.

En SQLite el formato de la fecha/hora es YYYY-MM-DD HH:MM.SS.MMM por ejemplo: 2019-06-22 01:00:27.123

Si bien Free Pascal tiene muchas funciones para el tratamiento de fecha y hora, a veces por la configuración regional hay que hacer conversiones.

Usando TFormatSettings:

aFormatSettings.LongDateFormat:='yyyy-mm-dd hh:nn:ss';
aFormatSettings.DateSeparator := '-';
aFormatSettings.TimeSeparator := ':';

Donde aFormatSettings es una variable del tipo TFormatSettings y luego utilizando la función FormatDateTime que devuelve un string:

function FormatDateTime(

const FormatStr: string;

DateTime: TDateTime;

Options: TFormatDateTimeOptions = []

):string;

function FormatDateTime(

const FormatStr: string;

DateTime: TDateTime;

const FormatSettings: TFormatSettings;

Options: TFormatDateTimeOptions = []

):string;

Por ejemplo en una sentencia SQL en WHERE:

sqlWhere:='WHERE regfecha BETWEEN '+QuotedStr(FormatDateTime('YYYY-MM-DD',edFechaDesde.Date))+
' AND '+QuotedStr(FormatDateTime('YYYY-MM-DD',edFechaHasta.Date))';

En este caso no fue necesario TFormatSettings y solo fue fecha, sin hora.

Con TZquery.FieldByName y utilizando componentes TDateTimePicker esto funciona:

ZQReg.FieldByName('regfechahora').AsDateTime:=dtpFecha.Date+dtpHora.Time;

En cuanto los campos del tipo TIME en SQLite; StrToTime('00:00:00') evita el valor nulo que muchas veces conviene evitar.

En SQLite la fecha debe ir entre comillas simples.

Y ante cualquier complicación siempre podemos hacer una función como la siguiente:

function FormatoFechaHoraSQLite(lafechahora: TDateTime): String;
var
  d,m,a,h,mi,s,ms:Word;
  dd,mm,hh,mmi,ss,mms:String;
  lfecha, lhora:String;
begin
  DecodeDate(lafechahora,a,m,d);
  if d < 10 then dd:='0'+IntToStr(d) else dd:=IntToStr(d);
  if m < 10 then mm:='0'+IntToStr(m) else mm:=IntToStr(m);
  lfecha:=IntToStr(a)+'-'+mm+'-'+dd;
  DecodeTime(lafechahora,h,mi,s,ms);
  if h < 10 then hh:='0'+IntToStr(h) else hh:=IntToStr(h);
  if mi < 10 then mmi:='0'+IntToStr(mi) else mmi:=IntToStr(mi);
  if s < 10 then ss:='0'+IntToStr(s) else ss:=IntToStr(s);
  if ms < 10 then mms:='00'+IntToStr(ms) else
    if ms < 100 then mms:='0'+IntToStr(ms) else
      mms:=IntToStr(ms);
  lhora:=hh+':'+mmi+':'+ss+'.'+mms;
  Result:=lfecha+' '+lhora;
end;

O más sencillo usando la función AddChar que se encuentra en la unidad strutils.

function FormatoFechaHoraSQLite2(lafechahora: TDateTime): String;
var
  d,m,a,h,mi,s,ms:Word;
  dd,mm,hh,mmi,ss,mms:String;
  lfecha, lhora:String;
begin
  DecodeDate(lafechahora,a,m,d);
  dd:=AddChar('0',IntToStr(d),2);
  mm:=AddChar('0',IntToStr(m),2);
  lfecha:=IntToStr(a)+'-'+mm+'-'+dd;
  DecodeTime(lafechahora,h,mi,s,ms);
  hh:=AddChar('0',IntToStr(h),2);
  mmi:=AddChar('0',IntToStr(mi),2);
  ss:=AddChar('0',IntToStr(s),2);
  mms:=AddChar('0',IntToStr(ms),3);
  lhora:=hh+':'+mmi+':'+ss+'.'+mms;
  Result:=lfecha+' '+lhora;
end;

DecodeDate y DecodeTime son procedimientos y utiliza variables del tipo Word que son enteros sin signo entre 0 y 65535. Le enviamos un TDateTime y las variables del tipo Word donde se escribirán el año, mes y día (en DecodeDate). No es necesario inicializar las variables, el procedimiento utiliza out:

procedure DecodeDate(

Date: TDateTime;

out Year: Word;

out Month: Word;

out Day: Word

);

TZConnection.ExecuteDirect y TZQuery.

Estos dos componentes pertenecen a Zeos Lib.
TZConnection se utiliza primariamente para establecer una conexión a una base de datos. Entre sus métodos está ExecuteDirect, una función sobrecargada (overloaded). Nada mejor que ver el código fuente:

function ExecuteDirect(SQL:string):boolean;overload;
function ExecuteDirect(SQL:string; var RowsAffected:integer):boolean;overload;

Si únicamente enviamos un parámetro, el string con la sentencia SQL, entonces la primera será llamada. Si usamos los dos parámetros, entonces se llamará a la segunda. Breve aclaración de overload.

Generalmente ExecuteDirect se utiliza para todo lo referido a actualizar la base de datos, por ejemplo: UPDATE, CREATE, DELETE, INSERT, VACUUM.

Como vemos, esta función siempre retorna un boolean en ambas versiones, que será True si hubo éxito o False si hubo un error.
Si además usamos el segundo parámetro, que como observamos es por referencia, el mismo se actualizará con el número de filas afectadas.

Por ejemplo, si mandamos VACUUM, siempre devolverá 0 (cero); en cambio si utilizamos un UPDATE nos devolverá la cantidad de registros actualizados.

Una vez más, veamos el código fuente de la implementación de las funciones:

{**
Executes the SQL statement immediately without the need of a TZQuery component
@param SQL the statement to be executed.
Returns an indication if execution was succesfull.
}

function TZAbstractConnection.ExecuteDirect(SQL : String) : boolean;
var
  dummy : Integer;
begin
  result:= ExecuteDirect(SQL,dummy);
end;

{**
Executes the SQL statement immediately without the need of a TZQuery component
@param SQL the statement to be executed.
@param RowsAffected the number of rows that were affected by the statement.
Returns an indication if execution was succesfull.
}

function TZAbstractConnection.ExecuteDirect(SQL: string; var RowsAffected: integer):boolean;
var
  stmt : IZStatement;
begin
  try
    try
      CheckConnected;
      stmt := DbcConnection.CreateStatement;
      RowsAffected:= stmt.ExecuteUpdate(SQL);
      result := (RowsAffected <> -1);
    except
      RowsAffected := -1;
      result := False;
      raise; {------ added by Henk 09-10-2012 --------}
    end;
  finally
    stmt:=nil;
  end;
end;

Ahora veamos la siguiente línea de código:

if ZConnection1.ExecuteDirect(Memo1.Text, n) then Memo1.Lines.Add('OK! '+IntToStr(n)+' filas.');

Sí bien es casi rídiculo hacer esto, un SELECT con ExecuteDirect, se puede, claro que siempre retornará cero, aunque la tabla tenga 500 filas.
En realidad también se puede, por ejemplo usar VACUUM desde un consulta TZQuery. El tema es saber cual de los dos métodos utilizar según lo que necesitemos, desde ya, una consulta será con TZQuery. Un INSERT puede ser tanto con ExecuteDirect o con los métodos de TZQuery, nuevamente, según lo que necesitemos y el estilo propio de cada programador.

Este UPDATE .. SET en realidad no hace nada, pero es válido como ejemplo, ExecuteDirect devuelve 51 en la variable n pasada por referencia, que desde ya, coincide con la cantidad total de filas de la tabla.

TZQuery:

De entrada conviene aclarar una especie de mito que hay de que siempre debe estar asociada con componente TDataSource, esto es falso, así de simple. Solo necesitaremos un TDataSource si los datos de la consulta deben ser mostrados en otros componentes, como ser un TDBGrid, TDBEdit, TDBLookUpComboBox, etc. Muchas veces veo ejemplos de LazReport donde se incluye innecesariamente un TDataSource.

Otra cosa elemental que no se debe intentar hacer, es editar una consulta que contiene JOIN, el error será inevitable. Es un error muy común tener una consulta con JOIN en un TDBGrid y querer actualizar una fila.

Para insertar un registro en una consulta del tipo SELECT campos FROM tabla (y puede también contener WHERE pero nunca JOIN), primero se debe invocar al método INSERT de TZQUery:

ZQuery1.Insert;

Luego, lo más común es utlizar el método FieldByName:

ZQuery1.FieldByName('nombre').asString:=edNombre.Text;
ZQuery1.FieldByName('edad').asInteger:=nEdad;

Y finalmente se usa el método Post para concretar la transacción. Si la propiedad Autocommit de TZConnection es True, entonces la transacción será inmediata:

ZQuery1.Post;

Un campo INTEGER con AUTOINCREMENT: al hacer un INSERT debe ignorarse siempre, ya sea utilizando ExecuteDirect o TZquery.

También puede utilizarse un TZQuery para un UPDATE, SET, etc.:


DataM.ZQa.Close;
DataM.ZQa.SQL.Text:='UPDATE reg SET saldo=saldo-:importe WHERE banco=:elbanco AND fecha>:regfecha;';
DataM.ZQa.Params.ParamByName('elbanco').AsInteger:=elbanco;
DataM.ZQa.Params.ParamByName('regfecha').AsString:=regfecha;
DataM.ZQa.Params.ParamByName('importe').AsCurrency:=importe;
DataM.ZQa.Open;
DataM.ZQa.Close;

Ventaja: se puede usar Params, algo que no se puede con ExecuteDirect.

¿Cómo usar dos tablas de distintas bases de datos en la misma consulta?

Para adjuntar una tabla a un DataSet, en este caso TZQuery, y que no produzca un error, la solución que no encontré en ningún lado, la descubrí mediante el sistema de prueba y error hasta que salga. Y como suele suceder cuando no se encuentra algo en toda la web, es porque ese algo, es muy obvio y este caso no fue la excepción.

Primero establecemos la conexión con una de las dos bases de datos mediante el componente TZConnection. Luego hacemos una consulta para adjuntar la base de datos, la abrimos y la cerramos, listo, ya está adjuntada. Luego escribimos la consulta que necesitemos.

Ejemplo:

ZQ.Close;
ZQ.SQL.Text:='ATTACH DATABASE '+QuotedStr(strDB)+' AS realgestdb;';
ZQ.Open;
ZQ.Close;
ZQ.SQL.Text:='SELECT cfecha, cprovid, nombre, ccomp, realgestdb.comprob.ccomprobalias, cletracomp, '+
'cnrocomp, ccaeocai, ccai, cuit, cnetogravado, cnogravado, cimpinternos, cpercib, cperciva, ctasaiva, '+
'civa, cnetogravado1, ctasaiva1, civa1, cnetogravado2, ctasaiva2, civa2, ctotal '+
'FROM ccompras '+
'INNER JOIN cprov ON cprovid=provid '+
'INNER JOIN realgestdb.comprob ON ccomp=realgestdb.comprob.id '+
'WHERE cfecha BETWEEN '+QuotedStr(desde)+' AND '+QuotedStr(hasta)+
'ORDER BY cfecha, cprovid ;';
ZQ.Open;

Las tres primera lineas realizan el ATTACH DATABE y ya queda disponible para cualquier consulta que se realice en el mismo dataset, hasta que se des adjunte, para ello:

ZQ.Close;
ZQ.SQL.Text:='DETACH DATABASE '+QuotedStr('realgestdb')+';';
ZQ.Open;
ZQ.Close;

Es importante para adjuntar, enviar el path completo de la base de datos y entre comillas simples, para eso nada más cómodo que la función QuotedStr. En este caso strDB es una variable del tipo string que contiene el path completo de la base de datos a adjuntar. Luego con AS le establecemos un alias para luego referenciarla en las consultas SQL. El alias puede ser cualquier nombre.
Pero ojo, que para realizar el DETACH DATABASE se utiliza el Alias, no el path completo de la base de datos. Esto se debe que, al menos SQLite, permite adjuntar variar veces una misma base de datos bajo distintos Alias (AS).

La cuarta línea cierra la consulta. La quinta, cambia la consulta y para acceder al campo ccomprobalias de la tabla comprob de la base de datos adjuntada bajo el alias de realgestdb lo hacemos de la forma Alias.tabla.campo.

TDataSource cuándo usarlo?

Lazarus, Free Pascal y muchos de sus componentes son sencillamente espectaculares, más aún teniendo en cuenta que es un proyecto de código abierto que comenzó hace muchos años (2001 creo), con pocos programadores para tamaño proyecto, actualmente entre Free Pascal y Lazarus, son unos 20.
El problema, lo dije desde un principio cuando retomé la programación, motivado por la existencia del IDE Lazarus, es la documentación, muchas veces inexistente y otras veces hay que dedicar mucho tiempo para dar con ella.
Los tutoriales para trabajar con bases de datos son pocos, algunos desactualizados y casi siempre los mismo ejemplos.
Es así como aprendí que para conectar y trabajar con una BD se necesitan 3 componentes: el conector (TZConnection), la consulta (TZQuery) y la fuente de datos (TDataSource). Pues no es así. Me di cuenta leyendo y participando del foro, cuando un usuario planteó una duda y detecté que en su código no utilizaba ningún TDataSource, le pregunté y me respondió si era necesario. Me sembró la duda y de hecho la respuesta es: No. Eso es cambia mucho mi panorama, para empezar no necesitaré tantos data modules si solo tengo un para de consultas, puedo declarar las conexiones y consultas sin necesidad de utilizar el data aware y que no molesten en el Form. Ahora entiendo por qué una vez me dijeron que cuando comienzas con esto, usas todo data aware y luego vas directamente por el código, y así es.

El data aware TDataSource solo es necesario cuando necesitamos enlazar los datos con un componente como puede ser un DBGrid, DBComboBox, etc. caso contrario no es necesario.

El ícono es muy claro, el componente envía los datos de un TDataSet hacia otros componentes, es indispensable para un TDBGrid por ejemplo, por eso es que el DataSource se debe vincular con un DataSet (y éste con un conector) y lo que alimenta al DBGrid es el DataSource. Ahora si no necesitamos alimentar ningún control, entonces no es necesario utilizar ningún DataSource.

SQL: Insertar registros en una tabla con campo auto increment.

¿Cómo ejecutar correctamente el comando SQL para insertar filas que contienen una columna auto incremental?

Por ejemplo, una tabla (tabla1) con 4 campos: id, nombre, apellido y edad.

CREATE TABLE tabla1 (id INTEGER UNIQUE NOT NULL PRIMARY KEY AUTOINCREMENT, nombre VARCHAR(50), apellido VARCHAR(50), edad INTEGER);

Error común:

INSERT INTO tabla1 VALUES ('Juan', 'Pérez', 25);

Esto arrojará un error del tipo "la tabla tiene 4 columnas pero solo se proporcionan 3 valores" y es cierto, pero claro, no se puede pasar el valor id porque el mismo debe establecerlo SQL.
La solución es simplemente especificar los campos:

INSERT INTO tabla1 (nombre, apellido, edad) VALUES ('Juan', 'Pérez', 25);

En caso de utilizar Zeos esto se realiza mediante ZConnection1.ExecuteDirect o también puede hacerse mediante el dataset ZQuery que sería algo así: (con la tabla ya creada)

ZQuery1.SQL.Text('SELECT * FROM tabla1;');
ZQuery1.Insert;
ZQuery1.FieldByName('nombre').AsString:='Juan';
ZQuery1.FieldByName('apellido').AsString:='Pérez';
ZQuery1.FieldByName('age').AsInteger:=25;
ZQuery1.Post

RecNo: obtener el registro actual de un DataSet

Un error muy común es intentar obtener y establecer el puntero de un DataSet desde la propiedad del mismo, ¿por qué? porque no está implementada y siempre retornará -1 o 0 (cero), se necesita acceder a ella a través de una clase descendiente que la implemente, por ejemplo, una query de SQL.

También es importante no confundir RecNo con RowID ni con la clave primaria ni con nada, es un puntero al registro actual y se manifiesta en forma de un número entero, un LongInt para ser más precisos, por ende puede almacenarse este valor en una variable del tipo Integer.

Ejemplo de forma incorrecta de leer la propiedad RecNo:

ZQuery.DataSource.DataSet.RecNo

La forma correcta es:

ZQuery.RecNo

Por ejemplo:

var
  RegistroActual:Integer;
begin
  RegistroActual:=ZQuery.RecNo;
  ZQuery.Close;
  //...Se hace algo....
  ZQuery.Open;
  ZQuery.RecNo:=RegistroActual;
end;

SQLite – Triggers: explicación y ejemplos

Un trigger es un evento que se lanza cuando ocurre algo determinado en una base de datos y se ejecuta una sentencia SQL. Ese “algo” que ocurre puede ser que se borre un registro (fila) de una determinada tabla, que se agregue un registro o que se modifique uno o más campos de una tabla, es decir, un INSERT, DELETE o UPDATE.

¿Cuándo se declara el trigger? Puede declararse en cualquier momento y siempre de acuerdo a las necesidades del caso, siendo lo normal hacerlo al momento de crear las tablas, como los índices y constraints.

No es necesario que las tablas actuantes en el trigger estén relacionadas mediante una Foreign Key.

Existe una IDE para SQLite muy práctica para este tipo de cosas, especialmente hasta que le tomemos la mano, nos es de gran ayuda, que es SQLiteStudio (está en inglés) y es de código abierto.

Uno puede, no obstante, prescindir de los triggers de SQLite y hacerlo a mano, es decir, mediante programación, en mi caso, con Free Pascal desde Lazarus, de hecho lo estuve haciendo hasta ahora, por “seguridad”, por preferir tener el control total especialmente en cuanto a la validación de por ejemplo valores duplicados, todavía lo hago, no me llevo bien con el manejo de excepciones, además SQLite carece de Stored Procedures, de momento son excusas válidas. Pero para borrar un registro en otra tabla, actualizar un dato, o agregar, casos simples, empecé a utilizar triggers, más precisamente hace dos días y por suerte además de funcionar correctamente, no es algo difícil de implementar.

Los ejemplos son casos reales de un programa de control de proveedores e insumos.


INSERT:

Un trigger que cree un registro en otra tabla cada vez que el usuario da de alta un insumo.

CREATE TRIGGER agregoinsu
         AFTER INSERT
            ON prod
BEGIN
    INSERT INTO movinsu (
                            idcompra,
                            idprod,
                            fecha,
                            cantidad,
                            precio,
                            total,
                            saldo
                        )
                        VALUES (
                            0,
                            new.id,
                            '2016-01-01',
                            new.ini,
                            0,
                            0,
                            0
                        );
END;

Al trigger lo nombré “agregoinsu”, luego hay que indicar cuando se dispara el trigger, antes o después y antes o después de ¿qué?, en este caso después (AFTER) de que se produjo el INSERT. ¿en qué tabla?, ON prod (en la tabla prod). Nota: la tabla prod debería llamarse insumos, pero se llama prod por motivos que no vale la pena aclarar. Hasta acá definimos cuando se lanza el trigger, ahora debemos indicar que se hace y eso lo hacemos mediante sentencias SQL comprendidas entre BEGIN y END; En este caso se indica que se inserte un registro en la tabla “movinsu” que posee un campo autoincremental “id” que se omite porque se encarga SQLite, el resto de las columnas o campos los especifico. Ahora debo definir los valores de esa nueva fila en “movinsu” y acá aparece la palabra new. Para triggers de inserción, se utiliza new, para triggers de eliminación se utiliza old y para triggers de actualización se pueden utilizar ambos (new y old). “new.id” significa que el nuevo registro en movinsu, en la columna idprod, irá el valor del nuevo (new) id (prod.id) y en la columna cantidad ira el valor “new.ini” que viene de prod.ini. Resumiendo, cada vez que se agregue, o dé de alta, un insumo (prod) se insertará una fila en la tabla movinsu con los valores especificados.

Este trigger debe definirse sobre la tabla que lanza el evento, en este caso, la tabla prod.

DELETE:

Lo contrario al ejemplo anterior, un trigger que elimine un registro en la tabla movinsu cuando se elimina un insumo de la tabla prod.

CREATE TRIGGER borroinsu
         AFTER DELETE
            ON prod
BEGIN
    DELETE FROM movinsu
          WHERE (idprod = old.id) AND
                (idcompra = 0);
END;

Este trigger es muy simple, AFTER (después) de DELETE (borrar) un registro ON (en) la tabla prod se ejecuta la sentencia SQL comprendida entre BEGIN y END, borrar de la tabla movinsu donde “old.id” sea igual a idprod (de la tabla movinsu). La otra condición idcompra=0 es porque con ese ID identifico el stock inicial; en realidad podría obviar esto, ya que antes de permitir borrar un insumos chequeo primero que no tenga movimientos registrados, si los tiene no permito su eliminación, pero por las dudas prefiero comprobarlo dos veces y no dejar que un trigger se dispare y borre alegremente todos los movimientos de un insumo, no nos olvidemos que cualquiera con un poco de conocimientos puede abrir la tabla y borrar un insumo y el trigger se dispara igual, por ejemplo, un usuario intenta borrar un insumo y el programa no lo deja porque tiene 20 registraciones, el usuario debe primero borrar todas las registraciones y luego borrar el insumo, puede tentarse de meter mano a la base de datos y borrarlo, pues bien, en este caso, el trigger con el condicional (idcompra=0) solo borrará el registro correspondiente al stock inicial dejando “vivas” las otras registraciones. Claro que si lo que se pretende es que el trigger arrase con todo quedaría así:

CREATE TRIGGER borroinsu
         AFTER DELETE
            ON prod
BEGIN
    DELETE FROM movinsu
          WHERE (idprod = old.id);
END;


“old.id” es el campo “id” del registro que se borró. Como es un trigger del tipo delete, solo se puede utilizar old para referenciar un campo.

UPDATE:

En update se puede usar tanto old como new para referenciar campos. Este trigger se lanza cuando se modifica el stock inicial de un insumo (campo “ini”) en la tabla prod y modifica el campo “cantidad” en la tabla movinsu.

CREATE TRIGGER modificoinsu
         AFTER UPDATE OF ini
            ON prod
BEGIN
    UPDATE movinsu
       SET cantidad = new.ini
     WHERE (idprod = new.id) AND
           (idcompra = 0);
END;

Traduciendo un poco, se lanza después (AFTER) de una modificación (UPDATE) del campo “ini” de la tabla prod (ON prod) y se ejecuta la actualización (UPDATE) de la tabla movinsu, se establece (SET) el campo “cantidad” con el valor de “new.ini” (el nuevo stock inicial) y la claúsula WHERE es la misma que el ejemplo anterior. Si bien el campo “id” no cambia, se debe referenciarlo con new o con old.

Son tres ejemplos sencillos para iniciarse en el tema triggers.

Documentación consultada: (en inglés)

https://sqlite.org/lang_createtrigger.html

https://www.tutorialspoint.com/sqlite/sqlite_triggers.htm