This topic describes the trigger functions in PL/SQL.
Triggers on data changes
In PL/SQL, a data change trigger is declared as a function with no parameters and a return type of trigger. Even if a data change trigger expects to receive some parameters that are specified in the CREATE TRIGGER statement, the data change trigger must be declared with no parameters. These parameters are passed by using TG_ARGV, as described in the following section.
When a PL/SQL function is invoked as a trigger, the following special variables are automatically created in the top-level block:
NEW: a variable of the RECORD data type. This variable holds the new row of data for INSERT and UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.
OLD: a variable of the RECORD data type. This variable holds the old row of data for UPDATE and DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.
TG_NAME: a variable of the NAME data type. This variable holds the name of the trigger that is fired.
TG_WHEN: a variable of the TEXT data type. This variable holds a string whose value is BEFORE, AFTER, or INSTEAD OF. The value varies based on the definition of the trigger that is fired.
TG_LEVEL: a variable of the TEXT data type. This variable holds a string whose value is ROW or STATEMENT. The value varies based on the definition of the trigger that is fired.
TG_OP: a variable of the TEXT data type. This variable holds a string whose value is INSERT, UPDATE, DELETE, or TRUNCATE. The value indicates the operation for which the trigger is fired.
TG_RELID: a variable of the OID data type. This variable holds the object ID of the table for which the trigger is fired.
TG_RELNAME: a variable of the NAME data type. This variable holds the name of the table for which the trigger is fired. This variable is discontinued and may be deleted in a future version. This variable is replaced with the TG_TABLE_NAME variable.
TG_TABLE_NAME: a variable of the NAME data type. This variable holds the name of the table for which the trigger is fired.
TG_TABLE_SCHEMA: a variable of the NAME data type. This variable holds the schema name of the table for which the trigger is fired.
TG_NARGS: a variable of the INTEGER data type. This variable holds the number of parameters that are provided to the trigger function in the CREATE TRIGGER statement.
TG_ARGV[]: a variable of the TEXT data type. This variable holds the parameters in the CREATE TRIGGER statement. The index counts from 0. An index whose value is less than 0 or no less than the value of the TG_NARGS variable is invalid. In this case, NULL is returned.
A trigger function must return NULL or a record or row value that exactly matches the schema of the table for which the trigger is fired.
A BEFORE row-level trigger can return NULL to instruct the trigger manager to skip the subsequent operation on a row. For example, the subsequent triggers are not fired, and the INSERT, UPDATE, or DELETE operation is not performed on this row. If a non-NULL value is returned, the operation on this row proceed. If the returned row value is different from the original value of the NEW variable, the row to be inserted or updated is modified. Therefore, if you want to use the trigger function to trigger an action without changing the value of the row, the original value of the NEW variable or another equivalent value must be returned. If you want to modify the row that is to be stored, you can directly replace single values in the NEW variable and return the modified NEW variable, or construct a new record or row to return. The return value of a BEFORE trigger for the DELETE operation has no direct effect. However, the return value must be a non-NULL value for the trigger action to proceed. The value of the NEW variable is NULL in a trigger for the DELETE operation, which makes no sense if returned. In most cases, a trigger for the DELETE operation returns the value of the OLD variable.
An INSTEAD OF trigger is always a row-level trigger and can only be used for views. A return value of NULL indicates that no updates are performed, and the subsequent operation on the row can be skipped. In this case, subsequent triggers are not fired and the row is not counted as an affected row by the surrounding INSERT, UPDATE, or DELETE operation. A non-NULL return value indicates that the requested operation has been performed by the trigger. The value of the NEW variable is returned for the INSERT and UPDATE operations. The trigger function may modify the value of the variable to support the INSERT RETURNING and UPDATE RETURNING operations. The modification also affects the row value that is passed to subsequent triggers or a special EXCLUDED alias reference within an INSERT statement with an ON CONFLICT DO UPDATE clause. The value of the OLD variable is returned for the DELETE operation.
The return value of an AFTER row-level trigger, a BEFORE statement-level trigger, or an AFTER statement-level trigger is always ignored or NULL. However, all these triggers can abort the entire operation by raising an error.
Sample PL/SQL trigger function
The following sample trigger provides an example on how to stamp the username and time into a row in a table when the row is inserted or updated. This trigger also checks whether the name of an employee is provided and whether the salary of the employee is a positive value.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
-- Verify that the values of empname and salary are not NULL.
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
- Record the time when the payroll is modified and the user who modifies the payroll.
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;You can also create a new table to hold a row for each INSERT, UPDATE, or DELETE operation on a table. You can use this method to audit the changes to a table.
Sample PL/SQL trigger function for auditing
The following sample trigger provides an example on how to use the emp_audit table to record the INSERT, UPDATE, and DELETE operations on the emp table. This way, the changes to the emp table are audited. The current time, username, and operation type are stamped into each row in the emp_audit table.
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 TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
--
-- Create a row in the emp_audit table to record the operation that is performed on the emp table.
-- You can use the special variable TG_OP to obtain the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
END IF;
RETURN NULL; -- Ignore the result of the AFTER trigger.
END;You can also use a view to join the main table to the audit table to display the time when each entry is modified. This way, a full audit trail is performed on the changes to a table, and a simplified view of the audit trail is provided. This view displays only the timestamp when the last change is made to each entry.
Sample PL/SQL view trigger function for auditing
In this example, a trigger is used to allow a view to be updated and ensure that the INSERT, UPDATE, or DELETE operation on a row in the view is recorded in the emp_audit table. The current time, username, and operation type are recorded, and the view displays the timestamp when the row is last modified.
CREATE TABLE emp (
empname text PRIMARY KEY,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer,
stamp timestamp NOT NULL
);
CREATE VIEW emp_view AS
SELECT e.empname,
e.salary,
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW
BEGIN
--
-- Perform the requested operation on the emp table and create a row in the emp_audit table to record the operation.
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', user, NEW.*);
RETURN NEW;
END IF;
END;A trigger can also be used to maintain a summary table of another table. The summary table can be used in place of the original table for some queries to accelerate the queries. This is commonly used in a data warehouse, in which the fact tables that contain the measured or observed data may be extremely large.
Sample PL/SQL trigger function for maintaining a summary table
The following schema is partly based on the example of Grocery Store from The Data Warehouse Toolkit by Ralph Kimball.
-- The main tables time_dimension and sales_fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- The summary table sales_summary_bytime.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- The function and trigger that are used to modify the columns of the summary table when INSERT, UPDATE, and DELETE operations are performed.
--
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Calculate the increment or decrement amounts.
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- Forbid updates that change the time_key field.
-- This is not troublesome because most changes are performed by using the DELETE and INSERT operations.
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update a row that contains the new values in the summary table.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- Do nothing.
END;
END LOOP insert_update;
RETURN NULL;
END;
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;AFTER triggers can also use transition tables to inspect the entire set of rows that are changed by the triggering statement. The CREATE TRIGGER statement assigns names to one or two transition tables. Then, the function can refer to those names as if they were read-only temporary tables.
A transition table can be used for auditing.
The following example produces the same results as the sample PL/SQL trigger function for auditing without using a trigger that fires for each row. In the following example, the related information in a transition table is collected and a trigger is fired once for each statement. This is faster than a row-trigger approach if you need to modify multiple rows by invoking statements. Take note that the REFERENCING clauses must be different for each case. Therefore, you must declare a trigger for each type of event. You can still use a single trigger function. However, we recommend that you use three separate trigger functions to prevent the test on the TG_OP variable.
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() RETURN TRIGGER IS
BEGIN
--
-- Create rows in the emp_audit table to record the operations performed on the emp table.
-- You can use the special variable TG_OP to obtain the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit
SELECT 'D', now(), user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit
SELECT 'U', now(), user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit
SELECT 'I', now(), user, n.* FROM new_table n;
END IF;
RETURN NULL; -- Ignore the result of the AFTER trigger.
END;
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();Triggers on events
PL/SQL allows you to define event triggers. In PolarDB for PostgreSQL(Compatible with Oracle), a function that is invoked as an event trigger must be declared as a function with no parameters and a return type of event_trigger.
When a PL/SQL function is invoked as an event trigger, the following special variables are automatically created in the top-level block:
TG_EVENT: a variable of the TEXT data type. This variable holds a string that indicates the event for which the trigger is fired.
TG_TAG: a variable of the TEXT data type. This variable holds the command tag for which the trigger is fired.
The following sample trigger provides an example on how to use an event trigger function in PL/SQL.
In this example, the trigger is used to raise a NOTICE message each time a supported command is run.
CREATE OR REPLACE FUNCTION snitch() RETURN event_trigger IS
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();