PostgreSQL Triggers are information base callback capacities, which are consequently performed/summoned when a predetermined data set occasion happens.
Coming up next are significant focuses about PostgreSQL triggers −
- PostgreSQL trigger can be determined to fire
- Before the activity is endeavored on a column (before limitations are checked and the INSERT, UPDATE or DELETE is endeavored)
- After the activity has finished (after requirements are checked and the INSERT, UPDATE, or DELETE has finished)
- Rather than the activity (on account of additions, refreshes or erases on a view)
- A trigger that is set apart FOR EACH ROW is called once for each line that the activity adjusts. Conversely, a trigger that is set apart FOR EACH STATEMENT just executes once for some random activity, paying little mind to the number of columns it alters.
- Both, the WHEN condition and the trigger activities, may get to components of the line being embedded, erased or refreshed utilizing references of the structure NEW.column-name and OLD.column-name, where segment name is the name of a section from the table that the trigger is related with.
- On the off chance that a WHEN provision is provided, the PostgreSQL explanations indicated are possibly executed for lines for which the WHEN condition is valid. On the off chance that no WHEN provision is provided, the PostgreSQL articulations are executed for all columns.
- On the off chance that various triggers of a similar kind are characterized for a similar occasion, they will be terminated in sequential request by name.
- The BEFORE, AFTER or INSTEAD OF watchword decides when the trigger activities will be executed comparative with the inclusion, adjustment or evacuation of the related column.
- Triggers are consequently dropped when the table that they are related with is dropped.
- The table to be changed should exist in a similar information base as the table or view to which the trigger is connected and one should utilize just tablename, not database.tablename.
- A CONSTRAINT choice when determined makes a limitation trigger. This is equivalent to a normal trigger aside from that the circumstance of the trigger terminating can be changed utilizing SET CONSTRAINTS. Imperative triggers are required to raise a special case when the limitations they actualize are abused.
Syntax
The fundamental punctuation of making a trigger is as per the following −
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- Trigger logic goes here....
];
Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE information base procedure on the referenced table table_name. You can alternatively indicate FOR EACH ROW after table name.
Coming up next is the grammar of making a trigger on an UPDATE procedure on at least one indicated sections of a table as follows −
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
-- Trigger logic goes here....
];
Example
Allow us to consider a situation where we need to keep review preliminary for each record being embedded in COMPANY table, which we will make recently as follows (Drop COMPANY table in the event that you as of now have it).
testdb=# CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
To keep review preliminary, we will make another table called AUDIT where log messages will be embedded at whatever point there is a section in COMPANY table for another record −
testdb=# CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
Here, ID is the AUDIT record ID, and EMP_ID is the ID, which will come from COMPANY table, and DATE will keep timestamp when the record will be made in COMPANY table. So now, let us make a trigger on COMPANY table as follows −
testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
Where auditlogfunc() is a PostgreSQL method and has the accompanying definition −
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;
Presently, we will begin the real work. Allow us to begin embeddings record in COMPANY table which should bring about making a review log record in AUDIT table. So let us make one record in COMPANY table as follows −
testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
This will make one record in COMPANY table, which is as per the following −
id | name | age | address | salary
----+------+-----+--------------+--------
1 | Paul | 32 | California | 20000
Same time, one record will be made in AUDIT table. This record is the consequence of a trigger, which we have made on INSERT procedure on COMPANY table. Essentially, you can make your triggers on UPDATE and DELETE activities dependent on your prerequisites.
emp_id | entry_date
--------+-------------------------------
1 | 2013-05-05 15:49:59.968+05:30
(1 row)
Listing TRIGGERS
You can list down all the triggers in the current data set from pg_trigger table as follows −
testdb=# SELECT * FROM pg_trigger;
The above given PostgreSQL articulation will list down all triggers.
In the event that you need to list the triggers on a specific table, at that point use AND statement with table name as follows −
testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';
The above given PostgreSQL proclamation will likewise list down just a single passage as follows −
tgname
-----------------
example_trigger
(1 row)
Dropping TRIGGERS
Coming up next is the DROP order, which can be utilized to drop a current trigger −
testdb=# DROP TRIGGER trigger_name;