Stored Procedures
and Triggers
1
Content
1. PostgreSQL Procedural Language (PL)
2. Trigger
3. Creating a Trigger Function
4. Testing the Trigger Function
2
1. PostgreSQL Procedural Language (PL)
PL = programming languages used by the database
engine à manipulate and extract data
PL execute directly inside the database engine, instead of
remotely in a separate application program.
- greatly improve performance by reducing the execution time
of your application
- provide a standard place to store functions that is accessible
to anyone who uses the database
3
Procedural languages
Standard PostgreSQL supports:
- PL/pgSQL
- PL/Tcl
- PL/Perl
- PL/Python
additional procedural languages available but not
included in the core distribution: PL/Java, PL/PHP,
PL/sh, …
To use = installed PL on the system
Default: PL/pgSQL is installed
4
PL/ pgSQL
NOT a standard language - it is specific to PostgreSQL
NOT portable to other database engines
HOWEVER:
- very close to Oracle’s default procedural language, PL/SQL
- very little effort is required to port functions created in
PL/SQL to PL/pgSQL, and visa versa
[Link]
5
[Link]
A trigger defines an operation that is performed when a
specific event occurs on a table/view:
- inserts a new record / updates an existing record, or deletes
a record.
The function executed as a result of a trigger is called a
trigger function.
- Trigger function format looks similar to the stored procedure
function (same CREATE OR REPLACE FUNCTION
command)
- 2 two things:
- Trigger functions do not use input arguments in the function, but
rather are passed arguments from a trigger event
6
- Trigger functions have access to special variables from the
database engine
3. CREATE TRIGGER command
CREATE TRIGGER name
{ BEFORE | AFTER | INSTEAD OF} {event [OR ... ] } ON table/view
[ FOR [ EACH ] { ROW | STATEMENT }]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function (arguments)
WHEN (condition) that determines whether the trigger function will
actually be executed
BEFORE, AFTER can be used for tables and views
INSTEAD OF can be only used for views at row-level
[Link]
7
CREATE TRIGGER command - Explain
Can occur either before or after the event occurs
(INSERT, UPDATE, DELETE, TRUNCATE on the table)
- multiple events can be specified using OR
- UPDATE events, it is possible to specify a list of columns using this
syntax: UPDATE OF column_name1 [, column_name2 ... ]
- INSTEAD OF UPDATE events do not support lists of columns.
(The TRUNCATE TABLE command deletes the data inside a table, but not
the table itself)
Fire triggers:
- ROW: for each row that is affected by the event
- STATEMENT: for each statement that triggers the event, no matter
how many rows are returned (even if no rows are returned)
8
CREATE TRIGGER command - Explain
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN ([Link] IS DISTINCT FROM [Link])
EXECUTE PROCEDURE check_account_update();
[Link]
[Link]
9
CREATE TRIGGER command
function: execute when the trigger is fired
- the arguments in the CREATE TRIGGER command are passed using
the TG_ARGV special variable
When a trigger function is called, the database engine passes a
group of special variables to the trigger function àdefine the
environment
- how the function was called
- what data is present when the trigger was fired
- when the trigger was fired
- …
10
[Link]
11 [Link]
4. Creating a Trigger Function
Code:
CREATE OR REPLACE FUNCTION trigger_func_name()
RETURNS trigger AS $$
[<variable declaration>]
BEGIN
<body code>
RETURN ....;
END;
$$ LANGUAGE plpgsql ;
12
4. Creating a Trigger Function
Can use the pgAdmin III program to create trigger functions:
- right-click the Trigger Functions object àselect New Trigger
Function
- Set the Language textbox to plpgsql
- A trigger function updates table records à VOLATILE function
- Parameter tab, NOT allowed to define arguments.
- Definition textbox àenter the function code
13
Example (previous class)
-- 2. Create a view from eduDB, named
student_class_shortinfos, this view contains:
student_id, firstname, lastname, gender, class
name.
create or replace view student_class_shortinfos
AS
select student_id, last_name, first_name ,
gender, dob, name
from student s left join clazz c on
s.clazz_id = c.clazz_id;
16
Example
-- define a INSTEAD OF INSERT trigger
CREATE TRIGGER insert_student_view
INSTEAD OF INSERT ON student_class_shortinfos
FOR EACH ROW
EXECUTE PROCEDURE
insert_view_student_class_shortinfos();
17
Example
-- define a trigger function
CREATE OR REPLACE FUNCTION
insert_view_student_class_shortinfos() RETURNS
trigger AS
$$
BEGIN
-- insert student
insert into student (student_id, last_name,
first_name , gender, dob) values (NEW.student_id,
NEW.last_name, NEW.first_name, [Link], [Link]);
RETURN NEW;
END;
$$ LANGUAGE plpgsql ;
18
Practice
Homework:
Define the triggers on view
“student_class_shortinfos” so that we can
insert, update, delete records from this
view
19
Remarks
RETURN in a trigger function
- NULL
- One record having the same structure as table record on which the
trigger is defined
Trigger « AFTER »:
- RETURN NULL; -- or RETURN NEW; RETURN OLD;
Trigger « BEFORE »
- RETURN NULL; : subsequent triggers are not fired, and the
INSERT/UPDATE/DELETE does not occur for this row
- Trigger BEFORE DELETE : RETURN OLD;
- Trigger BEFORE UPDATE OR INSERT: RETURN NEW;
20
Practice
Given EduBD:
student(student_id, first_name, last_name, dob, gender, address, note, class_id)
subject(subject_id, name, credit, percentage_final_exam)
lecturer(lecturer_id, first_name, last_name, dob, gender, address, email)
teaching(subject_id, lecturer_id)
clazz(clazz_id, name, lecturer_id, monitor_id, number_students)
enrollment(student_id, subject_id, semester, midterm_score, final_score)
21
Preparation
Add a new attribute (named: number_students, data type: integer) on
clazz table to store number of students in class.
Count the number of students in each class and update the correct
value for number_students attribute.
22
Practice
When a new student arrives (a new record is inserted into student table), the
number of students in her/his class must be automatically updated
-- define a trigger
CREATE TRIGGER af_insert
AFTER INSERT ON student
FOR EACH ROW
WHEN (NEW.clazz_id IS NOT NULL)
EXECUTE PROCEDURE tf_af_insert();
-- define a trigger function
CREATE OR REPLACE FUNCTION tf_af_insert() RETURNS TRIGGER AS $$
BEGIN
update clazz
set number_students = number_students+1
where clazz_id = NEW.clazz_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
23
Practice
Given EduBD, write triggers to ensure the following requirement:
• If data on student table is changed, the number of students in
clazz table is always correct.
(delete a student, change student class)
• Assuming that the number of students enrolled in a subject per
semester does not exceed 200, write a trigger that guarantees
this constraint.
(insert, update event on enrollment table)
24