Introduction to Triggers, PLpgSQL

1
Introduction to Triggers, PL/pgSQL

• Arif Emre Caglar

2
Trigger Behavior

• Triggers can be defined to execute either before
  or after any modifications to database.
• Former is called before triggers and the latter
  is called after triggers
• Modifications INSERT, UPDATE and DELETE
• Triggers can also be per-row triggers or
  per-statement triggers.

3
Examples of Usage

• Before trigger
• Inserting the current modification time into a
  timestamp column
• Check of the two elements of the row are
  consistent
• After trigger
• Propagate updates to other tables
• Make consistency checks against other tables

4
Return Values

• Per-statement triggers should always return null.
• Per-row triggers can return a table row
• If per-row trigger is also a before trigger
• Returning NULL skip the operation for the
  current row
• Returning a row Returned row will be inserted or
  will replace the row being updated.

5
Input Data

• Type of trigger event
• INSERT, UPDATE, DELETE
• Any argument that is listed in CREATE TRIGGER
• For the row level triggers
• new new row for INSERT and UPDATE queries
• old old row for UPDATE and DELETE queries

6
PL/pgSQL

• Triggers can be written in C or other available
  procedural languages supported by the server.
• We will use PL/pgSQL, the programming language of
  Postgresql.
• Not only used for triggers
• User defined functions, stored procedures.

7
PL/pgSQL Syntax

• Block structured language, the text of function
  definition must be a block.
• Syntax of a block
• label
• DECLARE
• declarations
• BEGIN
• statements
• END label

8
Declarations

• Some example declarations
• Userid integer
• Quantity numeric(5)
• url varchar
• Myrow tablenameROWTYPE
• myField tablename.columnnameTYPE
• aRow RECORD

9
Illustration of Pl/pgSQL and Triggers with a
Simple example

• Suppose there is a Student and an Exam table.
• Students take exams, and suppose that we did not
  use FOREIGN KEY constraint in exam table.
• Write a trigger function to enforce referential
  integrity between Exam and Student table.
• Student(sid integer, sname varchar(20))
• Exam(sid integer, cname varchar(15))

10
Create the Tables in PgAdmin

• CREATE TABLE Student(sid INTEGER, sname
  VARCHAR(20))
• CREATE TABLE Exam(sid INTEGER,cname VARCHAR(20))

11
Function written with PL/pgSQL to ensure
referential integrity

• CREATE OR REPLACE FUNCTION ref_int()
• RETURNS trigger AS
• DECLARE s RECORD
• BEGIN
• SELECT INTO s FROM Student WHERE NEW.sid
  sid
• IF NOT FOUND THEN
• RAISE NOTICE \No such student \, NEW.sid
• RETURN NULL
• ELSE
• RETURN NEW
• END IF
• END
• LANGUAGE PLpgSQL

12
Creating the Trigger

• CREATE TRIGGER ref_trigger
• BEFORE INSERT ON Exam
• FOR EACH ROW
• EXECUTE PROCEDURE ref_int()

13
Things to notice

• CREATE OR REPLACE Without this, if you want to
  change the definition of a function, you have to
  drop it first.
• AS ... The function body should be between
  the quotes. To use single quotes in body, you
  either have to type \ or
• DECLARE s RECORD s can store the information of
  a row.
• NEW.sid Since the trigger is before INSERTs, NEW
  corresponds to the new row to be inserted.
• RAISE NOTICE Used to display error text.
• RETURN NULL Skip the insertion for this row.
• RETURN NEW Insert the new row.

14
TRY

• Insert the following into Student table
• Insert INTO Student VALUES (1,Emre)
• Insert INTO Student VALUES (2,John)
• Insert the following into Exam table, since
  student 1 exists, trigger should allow the
  insertion
• Insert INTO Exam VALUES (1,Database I)
• The following insertion into Exam should not be
  allowed
• Insert INTO Exam VALUES (3, Database I)

15
Reference

• You can refer to PostgreSQL documentation (for
  version 7.4) for more information
• http//www.postgresql.org/docs/7.4/interactive/ind
  ex.html
• Note that syntax is a bit different in later
  versions.
• Check conditionals, loops, cursors, etc. if
  interested.

16
tutorial

• Brief discussion of normal forms
• BCNF if for every FD X -gt A, one of the
  following statements should be true
• A ?? X trivial FD.
• X is a superkey.
• 3NF
• One of the above conditions should hold or
• A is part of some key for R.
• 2NF
• X -gt A causes a violation of 3NF because X is a
  proper subset of some key K (partial dependency).
• If no partial dependency, then it is in 2NF.
• 1NF Relational model is automatically in 1NF.

17
Q 19.5

• Subrelations below are decomposed from a relation
  with attributes ABCDEFGHI. All the known FDs for
  this relation is listed for each question. For
  each subrelation, (a) State the strongest NF that
  the relation is in. (b) If its not in BCNF,
  decompose.
• R1(A,C,B,D,E), A? B, C?D
• R2(A,B,F), AC?E, B?F
• R3(A,D), D? G, G?H
• R4(D,C,H,G), A? I, I ? A
• R5(A,I,C,E)

18
Q19.3

• List all the FDs that this relation instance
  satisfies
• Assume the value of attribute Z of the last
  record is changed from z3 to z2. Now list all the
  FDs
• Answer Z?Y, X?Y, XZ?Y
• Answer unchanged if we changed z3 to z2.

19
A 19.5

• R1(A,C,B,D,E), A?B, C?D
• Key ACE.
• A and C are not superkeys, and none of the FDs
  are trivial. Not in BCNF
• Not in 3NF,B and D are not part of some key
• A and C are proper subsets of a key, not in 2NF.
• ACBDE
• A?B
• AB ACDE
• C?D
• CD ACE

20
A 19.5

• 2) R2(A,B,F), AC? E, B?F
• Key AB.
• Not in BCNF, B is not a superkey, no trivial FDs.
• Not in 3NF, F is not part of some key for R2.
• Not in 2NF, B is a proper subset of the key.
• BCNF Decomposition AB, BF
• 3) R3(A,D), D? G, G?H
• 4) R4(D,C,H,G), A? I , I? A
• 5) R5(A,I,C,E)
• In 3,4 and 5, no FDs affecting the relations, so
  they are in BCNF.

21
Q19.10

• Given R(A,B,C,D), for each of the following sets
  of FDs, (a) Identify the candidate key(s) for R,
  (b) State whether or not the proposed
  decomposition of R into smaller relations is
  good, why or why not?
• B?C, D?A decompose into BC and AD
• AB?C, C?A, C?D decompose into ACD and BC.
• A?BC, C?AD decompose into ABC and AD.
• A?B, B?C, C?D decompose into AB and ACD
• A?B, B?C, C?D decompose into AB, AD and CD

22
A19.10

• Candidate Key BD. This decomposition preserves
  dependencies but does not satisfy lossless join
  property. Not a good decomposition.
• Candidate Keys, AB and CB. This decomposition is
  lossless because ACD ? BC ? ACD. However, this
  decomposition does not preserve the dependency
  AB?C.
• Candidate keys, A and C. Thus, R is already in
  BCNF form, and there is no need to decompose it
  further.
• Candidate key A. This decomposition satisfies
  lossless join, but does not preserve B?C. Also,
  ACD is not even in 3NF, since C is not a superkey
  and D is not part of a key. Thus, not a good
  decomposition.
• Candidate key A. Same answer with 4.