SQLite | Trigger | Inspect a Trigger Schema

Tags:

This article explains two ways to inspect the CREATE statement used for a trigger: querying sqlite_master and using the .schema command.

Querying the sqlite_master Table

Query sqlite_master. Switching to line mode first makes the output easier to read.

select * from sqlite_master;

sqlite> .mode line
sqlite> select * from sqlite_master;
    type = table
    name = product
tbl_name = product
rootpage = 3
     sql = CREATE TABLE product (id integer, name text)

    type = table
    name = history
tbl_name = history
rootpage = 2
     sql = CREATE TABLE history (user text, name text, sales integer)

    type = trigger
    name = updateproduct
tbl_name = product
rootpage = 0
     sql = CREATE TRIGGER updateproduct update of name on product
begin
update history set name = new.name where name = old.name;
end

For a trigger, type is trigger. The name column contains the trigger name, tbl_name contains its target table, and sql contains the statement that created it. Add a WHERE clause to list triggers only.

select * from sqlite_master where type = 'trigger';

type = trigger
name = updateproduct
tbl_name = product
rootpage = 0
sql = CREATE TRIGGER updateproduct update of name on product
begin
update history set name = new.name where name = old.name;
end

Using the .schema Command

The .schema command also displays trigger schemas.

.schema
.schema? TABLE?

With no argument, it displays every table and trigger schema. When given a table name, it displays the matching table and associated triggers.

sqlite> .schema
CREATE TABLE product (id integer, name text);
CREATE TABLE history (user text, name text, sales integer);
CREATE TRIGGER updateproduct update of name on product
begin
update history set name = new.name where name = old.name;
end;

The output includes the CREATE statements for the two tables and one trigger in the current database.