How can I use a PostgreSQL triggers to store changes (SQL statements and row changes)
example of an audit trigger from
https://www.postgresql.org/docs/current/static/plpgsql-trigger.html
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
Postgresql: run trigger AFTER update FOR EACH STATEMENT ONLY if data changed
You should create two triggers: before update for each row
and after update for each statement
.
The first trigger checks if the table is being updated and sets a flag if so.
The second trigger checks the flag and performs notify
if it was set.
You can use a custom configuration parameter as the flag (e.g. flags.the_table
).
The solution is simple and safe, as the parameter is local in the current session.
create or replace function before_each_row_on_the_table()
returns trigger language plpgsql
as $$
begin
if new <> old then
set flags.the_table to 'on';
end if;
return new;
end $$;
create or replace function after_each_statement_on_the_table()
returns trigger language plpgsql
as $$
begin
if (select current_setting('flags.the_table')) = 'on' then
notify your_channel, 'the_table was updated';
set flags.the_table to 'off';
end if;
return null;
exception
when undefined_object then
-- occurs when flags.the_table was not defined
return null;
end $$;
create trigger before_each_row_on_the_table
before update on the_table
for each row execute procedure before_each_row_on_the_table();
create trigger after_each_statement_on_the_table
after update on the_table
for each statement execute procedure after_each_statement_on_the_table();
Postgresql 9.2 trigger to log changes to data in another table
I am only really interested in the three fields
Then it should be more efficient to only call the trigger after changes to these fields:
CREATE TRIGGER log_unitsref
AFTER UPDATE OF units, unitname, inuse
ON unitsref
FOR EACH ROW
WHEN (OLD.units, OLD.unitname, OLD.inuse) IS DISTINCT FROM
(NEW.units, NEW.unitname, NEW.inuse)
EXECUTE PROCEDURE log_unitsref();
I quote the manual on CREATE TRIGGER
:
UPDATE OF
...
The trigger will only fire if at least one of the listed columns is
mentioned as a target of the UPDATE command.
WHEN
...
A Boolean expression that determines whether the trigger function will
actually be executed.
Note that these two elements are closely related but neither mutually exclusive nor redundant.
It is much cheaper not to fire the trigger at all, if no column of interest is involved.
It is much cheaper not to execute the trigger function if no column of interest was actually altered.
Related answers here or here ...
How do I make a trigger to update a column in another table?
"mydatabase" is a remarkably unfortunate name for a schema.
The trigger function could look like this:
CREATE OR REPLACE FUNCTION trg_upaft_upd_trip()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
UPDATE mydatabase.trip t -- "mydatabase" = schema name (?!)
SET last_updated = now()
WHERE t.id = NEW.trip_id -- guessing column names
RETURN NULL; -- calling this AFTER UPDATE
END
$func$;
And needs to be used in a trigger on every related table (not on trip
itself):
CREATE TRIGGER upaft_upd_trip
AFTER UPDATE ON mydatabase.trip_detail
FOR EACH ROW EXECUTE PROCEDURE trg_upaft_upd_trip();
You also need to cover INSERT
and DELETE
(and possibly COPY
) on all sub-tables ...
This approach has many potential points of failure. As alternative, consider a query or view that computes the latest last_updated
from sub-tables dynamically. If you update often this might be the superior approach.
If you rarely UPDATE
and SELECT
often, your first approach might pay.
Trigger using adjusted OLD / NEW values
There is much amiss here:
trigger functions must return a row, typically (a modified)
NEW
, or NULL to abort the operationyou do not update
NEW
since it is not a table, but you assign to its columns, likeNEW.created := current_timestamp;
for
BEFORE DELETE
triggersNEW
is NULL, since there is no new version of the row
Which SQL statement can I use to re-execute the trigger and update all the rows in a table?
You have to update a column with exactly the same value. Of course it depends on the conditions for trigger. For instance:
UPDATE table
SET columnX = columnX;
Any way, as a best-practices for this cases I usually have and associated function in which I can run something like (not sure if works for progresql):
BEGIN
for c in (select column_id from table)
loop
function_on_trigger(c.column_id);
end loop;
END;
/
(Postgresql) make table trigger function insert ID value of changed row into log table, for custom replication
I was able to resolve this by changing AFTER to BEFORE in the trigger, which I believed at first wouldn't work, or:
CREATE OR REPLACE FUNCTION Insert() RETURNS TRIGGER AS $ReplInsert$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('DELETE', OLD.ID, OLD.NAME);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('UPDATE', NEW.ID ,NEW.NAME);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO "log" ("Operation","ID","NAME")
VALUES ('UPDATE', NEW.ID , NEW.NAME);
RETURN NEW;
END IF;
RETURN NULL;
END;
$ReplInsert$ LANGUAGE plpgsql;
CREATE TRIGGER logTrg
BEFORE INSERT OR UPDATE OR DELETE ON "FIRST_NAME"
FOR EACH ROW EXECUTE PROCEDURE Insert();
FOR EACH STATEMENT trigger example
OLD
and NEW
are null or not defined in a statement-level trigger. Per documentation:
NEW
Data type
RECORD
; variable holding the new database row forINSERT
/UPDATE
operations in row-level triggers. This variable is
null in statement-level triggers and forDELETE
operations.
OLD
Data type RECORD; variable holding the old database row for
UPDATE
/DELETE
operations in row-level triggers. This variable is null in statement-level triggers and forINSERT
operations.
Bold emphasis mine.
Up to Postgres 10 this read slightly different, much to the same effect, though:
... This variable is unassigned in statement-level triggers. ...
While those record variables are still of no use for statement level triggers, a new feature very much is:
Transition tables in Postgres 10+
Postgres 10 introduced transition tables. Those allow access to the whole set of affected rows. The manual:
AFTER
triggers can also make use of transition tables to inspect the entire set of rows changed by the triggering statement.
TheCREATE TRIGGER
command assigns names to one or both transition
tables, and then the function can refer to those names as though they
were read-only temporary tables. Example 43.7 shows an example.
Follow the link to the manual for code examples.
Example statement-level trigger without transition tables
Before the advent of transition tables, those were even less common. A useful example is to send notifications after certain DML commands.
Here is a basic version of what I use:
-- Generic trigger function, can be used for multiple triggers:
CREATE OR REPLACE FUNCTION trg_notify_after()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
PERFORM pg_notify(TG_TABLE_NAME, TG_OP);
RETURN NULL;
END
$func$;
-- Trigger
CREATE TRIGGER notify_after
AFTER INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_notify_after();
For Postgres 11 or later use the equivalent, less confusing syntax:
...
EXECUTE FUNCTION trg_notify_after();
See:
- Trigger function does not exist, but I am pretty sure it does
Update newly created row value with a trigger
I end up with the following solution. I made a BEFORE
trigger. The problem was the LEFT JOIN
with reference to the table where the new row doesn't exist yet. It's not ideal but here it is:
CREATE TRIGGER tsvectorupdate
BEFORE INSERT OR UPDATE
ON kkw_block
FOR EACH ROW
EXECUTE PROCEDURE kkw_search_trigger();
CREATE TYPE kkw_type_record_type AS (typ_abr TEXT, typ_desc_en TEXT, typ_desc_short_en TEXT);
CREATE TYPE kkw_record_type AS (kkw_name_en TEXT);
CREATE OR REPLACE FUNCTION kkw_search_trigger()
RETURNS trigger AS
$BODY$
DECLARE kkw_rec kkw_record_type;
DECLARE kkw_typ_rec kkw_type_record_type;
DECLARE vector_en TEXT;
BEGIN
--- make a individual select instead of LEFT JOIN
SELECT kkw_name_en INTO kkw_rec.kkw_name_en
FROM kkw
WHERE kkw.kkw_id = NEW.kkw_id;
--- make a individual select instead of LEFT JOIN
SELECT typ_abr, typ_desc_en, typ_desc_short_en INTO kkw_typ_rec.typ_abr, kkw_typ_rec.typ_desc_en, kkw_typ_rec.typ_desc_short_en
FROM kkw_typ
WHERE kkw_typ.kkw_typ_id = NEW.kkw_typ_id;
vector_en := coalesce(NEW.modell_en, '') || ', ' || coalesce(NEW.bezeichnung_en,'') || ', ' || coalesce(kkw_rec.kkw_name_en,'') || ', ' || coalesce(kkw_typ_rec.typ_abr,'') || ', ' || coalesce(kkw_typ_rec.typ_desc_en,'') || ', ' || coalesce(kkw_typ_rec.typ_desc_short_en,'');
NEW.search_vector_en := to_tsvector('english', vector_en);
RETURN NEW;
END
$BODY$
Related Topics
Oracle - What Statements Need to Be Committed
What Should Be the Best Way to Store a Percent Value in SQL-Server
SQL Return Only Duplicate Rows
Which Is Better: Bookmark/Key Lookup or Index Seek
Is There Something Equivalent to Argmax in SQL
Postgres - Create Table from Select
Ora-01843 Not a Valid Month- Comparing Dates
Postgresql Alter Column Data Type to Timestamp Without Time Zone
Regular Expression to Remove Comments from SQL Statement
How to Identify Port Number of SQL Server
Using SQL Count in a Case Statement
Between Clause Versus <= and >=
How to Insert a Unique Id into Each SQLite Row
Postgresql 9.1: How to Concatenate Rows in Array Without Duplicates, Join Another Table