Database Reference
In-Depth Information
It's time to query the
warehouse_audit
table to see whether it recorded the changes
we made on the
warehouse_tbl
table as follows:
warehouse_db=# SELECT wlog_id, insertion_time FROM
warehouse_audit;
wlog_id | insertion_time
---------+----------------------------
4 | 2014-10-21 16:33:08.202+03
5 | 2014-10-21 16:33:20.834+03
4 | 2014-10-21 16:33:34.448+03
4 | 2014-10-21 16:33:49.842+03
(4 rows)
Changes are successfully logged along with the type of the operation performed on
the
warehouse_tbl
table.
Creating triggers on views
We said earlier that triggers can be attached to views as well. This example will
demonstrate how to create triggers on views. We'll make use of a simple table called
tab_view
and a view called
view_select
.
First, create the
tab_view
table as follows:
warehouse_db=# CREATE TABLE tab_view
(
emp_id INT NOT NULL,
emp_name VARCHAR(10),
emp_city VARCHAR(10)
);
Let's insert a few rows in the table in the following manner:
warehouse_db=# INSERT INTO tab_view VALUES (1, 'Adam', 'Chicago');
warehouse_db=# INSERT INTO tab_view VALUES (2, 'John', 'Miami');
warehouse_db=# INSERT INTO tab_view VALUES (3, 'Smith', 'Dallas');
Create the
view_select
view as follows:
warehouse_db=# CREATE VIEW view_select AS SELECT * FROM tab_view;
Generally before PostgreSQL 9.3, if you try to update a view, it won't
allow you to do so as views are
read only
, so you have to user triggers
or rules to create such a mechanism. The latest versions of PostgreSQL
9.3 and onwards support the functionality of auto-updateable views.
