Database Reference
In-Depth Information
We will deine our problem irst. Let's begin with an easy-to-understand
example where we simply track records of any new row being inserted into the
warehouse_tbl
table. This example uses only one trigger, one trigger function, and
another table to log changes inserted by a trigger. We can set the search path using the
following statement:
warehouse_db=# set search_path='record';
Let's create the
warehouse_tbl
table now in the following manner:
warehouse_db=# CREATE TABLE warehouse_tbl
(
warehouse_id INTEGER NOT NULL,
warehouse_name TEXT NOT NULL,
year_created INTEGER,
street_address TEXT,
city CHARACTER VARYING(100),
state CHARACTER VARYING(2),
zip CHARACTER VARYING(10),
CONSTRAINT "PRIM_KEY" PRIMARY KEY (warehouse_id)
);
Now, create the
warehouse_audit
table in the following manner:
warehouse_db=# CREATE TABLE warehouse_audit
(
wlog_id INT NOT NULL,
insertion_time TEXT NOT NULL
);
Assuming that the
warehouse_tbl
table is empty, we will insert a few rows before
we deine any trigger and trigger function, as follows:
warehouse_db=# INSERT INTO warehouse_tbl
(warehouse_id, warehouse_name, year_created, street_address,
city, state, zip)
VALUES
(1, 'Mark Corp', 2009, '207-F Main Service Road East', 'New
London', 'CT', 4321);
warehouse_db=# INSERT INTO warehouse_tbl
(warehouse_id, warehouse_name, year_created, street_address,
city, state, zip)
VALUES
(2, 'Bill & Co', 2014, 'Lilly Road', 'New London', 'CT', 4321);
