PostgreSQL CREATE TRIGGER Trigger

knowledge points for triggers:

1. PostgreSQL executes/invokes the trigger in the following cases: Before attempting the operation (checking for constraints and trying INSERT , UPDATE or DELETE before). Or after the operation is complete (after checking for constraints and INSERT , UPDATE or DELETE after completion). Or not action (in view INSERT , UPDATE or in DELETE case of)

2. For each row modified by the operation, a trigger that is marked will be called FOR EACH ROWS . On the other hand, a trigger that is marked is FOR EACH STATEMENT executed only once for any given operation, regardless of how many rows it modifies.

3. You can define multiple triggers of the same type for the same event, but the condition is triggered alphabetically by name.

4. When the table associated with them is deleted, the trigger is automatically deleted.

Step1: Create a log table to track all operations on a record table

CREATE TABLE Auditlog
(
Operation varchar (100),
ID int,
ProductName varchar (100),
Catagoryid int,
Subcatagoryid int,
Operationdate Timestamp without time zone
)

Step2: Creating a trigger-related stored procedure

CREATE FUNCTION Public.auditinsertfunlog ()
RETURNS Trigger
LANGUAGE ' Plpgsql '
Cost 100
VOLATILE not leakproof
ROWS 0
As $BODY $

Begin
Insert into Auditlog (operation,id,productname,catagoryid,subcatagoryid,operationdate)
VALUES (' Insert ', new.id,new. ProductName ", New." Catagoryid ", New." Subcatagoryid ", current_timestamp);
return new;
End

$BODY $;

ALTER FUNCTION Public.auditinsertfunlog ()
OWNER to Postgres;

Step3: Creating a Trigger

CREATE TRIGGER Products_insert_trigger
After INSERT to public. " Products "
For each ROW EXECUTE PROCEDURE public.auditinsertfunlog ();

　　

PostgreSQL CREATE TRIGGER Trigger