Database Reference
In-Depth Information
Unfortunately, we cannot simply add
OR TRUNCATE
in the preceding trigger defini-
tion. The
TRUNCATE
command does not act on single rows, and so
FOR EACH ROW
triggers make no sense for truncate and are not supported.
You need to have a separate trigger definition for
TRUNCATE
. Fortunately, we can
still use the same function, at least for this simple "Hey, I'm called!" trigger:
CREATE TRIGGER notify_truncate_trigger
AFTER TRUNCATE ON notify_test
FOR EACH STATEMENT
EXECUTE PROCEDURE notify_trigger();
And now we get a notification on
TRUNCATE
as well:
postgres=# TRUNCATE notify_test;
NOTICE: Hi, I got notify_truncate_trigger
invoked FOR STATEMENT AFTER TRUNCATE on
notify_test
TRUNCATE TABLE
While getting these messages at each
Data Management Language
(
DML
) opera-
tion is cool, it has little production value.
So, let's develop this a bit further and log the event in an audit log table instead of
sending something back to the user.