PowerPoint Presentation
Professor: Dr. Shu-Ching Chen
TA: Sheng Guan
Trigger used in PosgreSQL
Introduction on Triggers
A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.
COST in stored procedures: A positive number giving the estimated execution cost for the
function, in units of cpu_operator_cost. If the function returns a
set, this is the cost per returned row. If the cost is not specified,
1 unit is assumed for C-language and internal functions, and 100 units
for functions in all other languages. Larger values cause the planner
to try to avoid evaluating the function more often than necessary. ”
2
Benefits of using triggers
Fast application development. Because the database stores triggers, you do not have to code the trigger actions into each database application.
Global enforcement, define a trigger once and then reuse it for any application that uses the database.
Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
Improve performance in client/server environment. All rules run in the server before the result returns.
3
Trigger(1)
Trigger that fires before event
Trigger that fires after event
in the case of inserts, updates or deletes on a view
PostgreSQL provides two kinds of triggers: row level trigger and statement level trigger, which can be specified by FOR EACH ROW (row level trigger) or FOR EACH STATEMENT (statement level trigger).
CREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table or view and will execute the specified function function_name when certain events occur.
The trigger can be specified to fire before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted); or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed); or instead of the operation (in the case of inserts, updates or deletes on a view). If the trigger fires before or instead of the event, the trigger can skip the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only). If the trigger fires after the event, all changes, including the effects of other triggers, are “visible” to the trigger.
A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. For example, a DELETE that affects 10 rows will cause any ON DELETE triggers on the target relation to be called 10 separate times, once for each deleted row. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies (in particular, an operation that modifies zero rows will still result in the execution of any applicable FOR EACH STATEMENT triggers).
4
Trigger(2)
Triggers that are specified to fire INSTEAD OF the trigger event must be marked FOR EACH ROW, and can only be defined on views. BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT.
A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. For example, a DELETE that affects 10 rows will cause any ON DELETE triggers on the target relation to be called 10 separate times, once for each deleted row. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies
The following table summarizes which types of triggers may be used on tables and views:
Truncate — cutting off the top or the end
5
Trigger(3)
In row-level triggers the WHEN condition can examine the old and/or new values of columns of the row. Statement-level triggers can also have WHEN conditions, although the feature is not so useful for them since the condition cannot refer to any values in the table.
SELECT does not modify any rows so you cannot create SELECT triggers. Rules and views are more appropriate in such cases.
Also, a trigger definition can specify a Boolean WHEN condition, which will be tested to see whether the trigger should be fired
6
Trigger – Simple Example
CREATE TABLE employees(
id int4 serial primary key,
first_name varchar(40) NOT NULL,
last_name varchar(40) NOT NULL
);
Whenever employee’s last name changes, we will log it into a separate table named employee_audits through a trigger.
Trigger – Simple Example
CREATE TABLE employee_audits (
id int4 serial primary key,
employee_id int4 NOT NULL,
last_name varchar(40) NOT NULL,
changed_on timestamp(6) NOT NULL
)
First, we define a new function called ——-log_last_name_changes
Trigger – Simple Example
CREATE OR REPLACE FUNCTION log_last_name_changes()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.last_name <> OLD.last_name THEN
INSERT INTO employee_audits(employee_id,last_name,changed_on)
VALUES(OLD.id,OLD.last_name,now());
END IF;
RETURN NEW;
END;
$BODY$
Trigger – Simple Example
Second, we bind the trigger function to the employees table. The trigger name is last_name_changes. Before the value of the last_name column is updated, the trigger function is automatically invoked into log the changes.
CREATE TRIGGER last_name_changes
BEFORE UPDATE
ON employees
FOR EACH ROW
EXECUTE PROCEDURE log_last_name_changes();
Trigger – Simple Example
Third, we can insert some sample data for testing. We insert two rows into the employees table.
INSERT INTO employees (first_name, last_name)
VALUES (‘John’, ‘Doe’);
INSERT INTO employees (first_name, last_name)
VALUES (‘Lily’, ‘Bush’);
SELECT * FROM employees;
Trigger – Simple Example
Suppose Lily Bush gets married and she needs to change her last name to Lily Brown. We can update it as the following query:
UPDATE employees
SET last_name = ‘Brown’
WHERE ID = 2;
SELECT * FROM employees;
Trigger – Simple Example
As you see, the Lily’s last name has been updated. Let’s check the employee_audits table:
SELECT *
FROM employee_audits;
Trigger – More Examples
PostgreSQL Trigger : Example AFTER INSERT
Example BEFORE INSERT
Example AFTER UPDATE
Example BEFORE UPDATE
Example AFTER DELETE
http://www.w3resource.com/PostgreSQL/postgresql-triggers.php
The following table summarizes which types of triggers may be used on tables and views:
In FOR EACH ROW triggers, the WHEN condition can refer to columns of the old and/or new row values by writing OLD.column_name or NEW.column_name respectively. Of course, INSERT triggers cannot refer to OLD and DELETE triggers cannot refer to NEW.
14
Trigger(4)
DROP TRIGGER removes an existing trigger definition.:
ALTER TRIGGER changes properties of an existing trigger. The RENAME clause changes the name of the given trigger without otherwise changing the trigger definition.
The following table summarizes which types of triggers may be used on tables and views:
15
Reference
PostgreSQL Manuals PostgreSQL 9.2
https://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html
Practical PostgreSQL
http://www.postgresqltutorial.com/creating-first-trigger-postgresql/
http://www.w3resource.com/PostgreSQL/postgresql-triggers.php
COST in stored procedures: A positive number giving the estimated execution cost for the
function, in units of cpu_operator_cost. If the function returns a
set, this is the cost per returned row. If the cost is not specified,
1 unit is assumed for C-language and internal functions, and 100 units
for functions in all other languages. Larger values cause the planner
to try to avoid evaluating the function more often than necessary. ”
16
Stored Procedure in PgAdmin
1
2
3
Stored Procedure in PgAdmin
/docProps/thumbnail.jpeg