Database Applications (NDBI026) SS2021/22

course:

Database Applications (NDBI026)

SS2021/22

RNDr. Michal Kopecký, Ph.D.

Department of Software Engineering, Faculty of Mathematics and Physics, Charles University in Prague

 Triggers

 Triggers – Overview

 Creating triggers

 Using Triggers

 Set operators

 UNION [ALL], INTERSECT, EXCEPT

 WITH SELECT Statements

 CASE Expressions

 Active database = a database that can respond to conditions both inside and outside the

database.

 Possible uses

 Security monitoring,

 Alerting,

 Statistics gathering,

 Authorization

 ECA rules (Event – Condition – Action)

 Triggers

 Trigger = Special type of stored procedure

 Activated automatically according to events if predefined condition is met on the server

▪ Data modification (described later)

▪ Events attached to database or session

▪ Logging-in/-out

▪ Table creation

▪ etc.

 Rules

Event  Condition  Action

 Event … data modification (INSERT/UPDATE/DELETE)

 Condition … changed data correspond

to condition, defined by Boolean SQL expression

 Action … defined trigger body containing

additional INSERT/UPDATE/DELETE …

statements is executed

 More detailed rules than integrity constraints

 Implemented directly in the database

 Applied equally for all client applications and sessions

 Active database allows to enforce broader

variety of integrity requirements than passive

database, where additional tests have to be

implemented on clients and/or application

servers.

 Triggers (rules) can be named by any identifiers

 It is advisable to construct names

systematically in such a way, that the name

suggests the name of the associated table,

the operation they react on and other useful

information

 Two types of triggers (active database rules)

 Row-level triggers

▪ They are activated by the change of individual rows of the associated table

▪ If more rows are changed, they can test data of individual rows

 Statement level triggers

▪ They are activated only once by the action itself

independently on the data content and changes

 Detailed checking of changed data, not reachable by integrity constraints

 Typically by BEFORE row-level triggers

 Checking that the user is allowed to execute given statement

 Typically by BEFORE statement-level triggers

 Journaling of old table values

 Typically by AFTER row-level triggers

 Journaling of the operation history on the table

 Typically by AFTER statement-level triggers

 Data replication

 All modifications are repeated on other (remote) table

 Typically by AFTER row-level triggers

 Additional fixing of errors in application logic

 Part of the application logic is “hidden”

 Changes in data has unpredictable side-effects

 Worse application debugging

 It is better to use procedures, that the applications can (or have to due to inability to change data

directly) explicitly execute

 CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER}

{INSERT | DELETE | UPDATE [OF column_name]}

ON table_name [FOR EACH ROW]

[REFERENCING OLD AS old_name NEW AS new_name]

[WHEN (condition)]

PL/SQL block;

 Defines what to be done before, resp. after the change of data in the database

 Row-level triggers are executed repeatedly for each changed

row and see the old and new version of the row

 Table actualization is done as follows:

1. BEFORE statement-level triggers 2. BEFORE row-level triggers

3. Row actualization / integrity constraint checking 4. AFTER row-level triggers

5. AFTER statement-level triggers

 If any of those steps fails,

ie. The unhandled exception is thrown, actualization is invalid and is not done

 There can be defined more triggers of the same

type on the given table, but their exact order is not

defined

 {CREATE|ALTER} TRIGGER trigger_name ON { table | view }

[ WITH ENCRYPTION ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ]

AS [{IF UPDATE ( column ) [{ AND | OR } UPDATE ( column ) ] … | IF ( COLUMNS_UPDATED ( bitwise_operator

updated_bitmask )}]

sql_statement [...]

 FOR is older equivalent for AFTER, no statement-level triggers

 The trigger can see and access all changed data using

pseudo-tables DELETED AND INSERTED

 Table actualization is done as follows : 1. Integrity constraint checking

2. Creating of tables deleted a inserted

2. Actualization (INSTEAD OF trigger) / integrity constraint checking

3. AFTER triggers

 If the transaction is found to be incorrect the

transaction can be rolled back using ROLLBACK TRANSACTION

 The order of execution for triggers of the same

type can be defined using sp_settriggerorder

procedure

 More types of actualization can be defined for one trigger

 In Oracle using OR

▪ CREATE TRIGGER …

BEFORE INSERT OR UPDATE ON …

▪ In the trigger body are defined Boolean predicates INSERTING, UPDATING, DELETING

 In MS SQL using commas

▪ CREATE TRIGGER … ON …

FOR INSERT, UPDATE

 INSTEAD OF triggers overrides the

implementation of the data actualization itself

 They are executed instead of INSERT, UPDATE, DELETE operation

 In Oracle they are allowed only for views, not for tables

 MS SQL doesn’t distinguish between tables and

views

 Scenario I:

Checking if the user is allowed to do given operation

 Executed usually – if possible – once before the statement is executed

▪ It can be tested if the user is logged from internal secure

terminal, in working hours and many other things not

available using integrity constraints

 Example:

 CREATE OR REPLACE TRIGGER bef_ins_Emp BEFORE INSERT

ON Emp BEGIN

IF USERENV(’TERMINAL’) NOT LIKE ’PERS%’

THEN

RAISE_APPLICATION_ERROR(

-20001,

’Employees can be hired only from Human resource department’

);

END IF;

END;

/

 Example:

 CREATE TRIGGER aft_ins_Emp ON Emp

AFTER INSERT AS

BEGIN

IF HOST_NAME() NOT LIKE ’PERS%’

RAISERROR(

'Employees can be hired only from Human resource department', 15, 1);

ROLLBACK TRANSACTION;

END;

 Scenario II:

Computing of functionally dependent values in the table

 Useful for fast search according to such functional dependent value

▪ Not necessary if the database is able to index expressions

 Executed usually – if possible – before the

actualization on each row

 There are defined two records inside row- level triggers – NEW and OLD. They contain row data before and after change.

 INSERT triggers only NEW record

 DELETE triggers only OLD record

 UPDATE triggers both OLD and NEW records

 Names of records can be changed using

REFERENCING clause

 Example:

 CREATE OR REPLACE TRIGGER bef_ins_upd_Emp_row BEFORE INSERT OR UPDATE

ON Emp

FOR EACH ROW BEGIN

-- for searching by total income :NEW.Income :=

COALESCE(:NEW.Sal,0)+COALESCE(NEW.Comm,0);

END;

/

 Trigger execution can be restricted by a Boolean condition declared in the WHEN condition

 CREATE OR REPLACE TRIGGER bef_ins_Emp_row BEFORE INSERT ON Emp FOR EACH ROW

WHEN (NEW.EmpNo IS NULL)

-- there is no colon sign in the WHEN clause before NEW and/or OLD BEGIN

SELECT SeqEmpNo.nextval INTO :NEW.EmpNo

FROM DUAL;

END;

/

 There are two pseudo-tables visible in the body of each trigger: inserted and deleted that contain new and old version of all

changed records

 deleted in AFTER DELETE, UPDATE trigger

 inserted in AFTER INSERT, UPDATE triggers

 Can be tested by:

IF EXISTS(SELECT * FROM DELETED) IF EXISTS(SELECT * FROM INSERTED)

▪ T/F … DELETE, T/T … UPDATE, F/T … INSERT

 Example:

 CREATE TRIGGER aft_ins_upd_Emp_row ON Emp

AFTER INSERT, UPDATE AS

BEGIN

-- for searching by total income UPDATE Emp SET

Income = COALESCE(Sal,0)+COALESCE(Comm,0) WHERE EpmNo IN (SELECT EmpNo FROM inserted)

AND (UPDATED(EmpNo) OR UPDATED(Comm));

END;

 Have to do it after operation using pseudo-

tables deleted and inserted.

 Scenario III:

Checking of inserted/updated data

 Executed – if available – before the operation and

for each row

 Example:

 CREATE OR REPLACE TRIGGER bef_upd_Emp_row BEFORE UPDATE

ON Emp FOR EACH ROW BEGIN

IF :NEW.Sal > :OLD.Sal * 1.1 THEN RAISE_APPLICATION_ERROR(

-21001,’Salary can be raised at most by 10%.’

);

END IF;

END;

 Example:

 CREATE TRIGGER aft_upd_Emp_row ON Emp

AFTER UPDATE BEGIN

IF 0 < (

SELECT COUNT(*) FROM Emp AS NEW

INNER JOIN INSERTED AS OLD ON (OLD.EmpNo=NEW.EmpNo) WHERE NEW.Sal > OLD.Sal * 1.1)

RAISERROR('Salary can be raised at most by 10%', 15, 1 );

ROLLBACK TRANSACTION;

END;

 Have to do it after operation using pseudo-tables

deleted and inserted.

 Example:

 CREATE TRIGGER aft_upd_Emp_row ON Emp

AFTER UPDATE BEGIN

IF EXISTS(

SELECT *

FROM Emp AS NEW

INNER JOIN INSERTED AS OLD ON (OLD.EmpNo=NEW.EmpNo) WHERE NEW.Sal > OLD.Sal * 1.1)

RAISERROR('Salary can be raised at most by 10%', 15, 1 );

ROLLBACK TRANSACTION;

END;

 Have to do it after operation using pseudo-tables

deleted and inserted.

 Scenario IV:

Journaling of data changes.

 Should be done after successful change in the main table.

 It is possible to remember author of the change,

time of change, original and new values etc.

 Example:

 CREATE OR REPLACE TRIGGER aft_upd_Emp_row AFTER UPDATE

ON Emp

FOR EACH ROW

WHEN (OLD.Sal <> NEW.Sal) BEGIN

INSERT INTO Journal(Who, When, EmpNo, OldSal, NewSal) VALUES(USER, SYSDATE, :OLD.EmpNo, :OLD.Sal, :NEW.Sal);

END;

 Example:

 CREATE TRIGGER aft_upd_Emp_row ON Emp

AFTER UPDATE AS

BEGIN

INSERT INTO Journal(Who, When, EmpNo, OldSal, NewSal) SELECT CURRENT_USER, GetDate(), d.EmpNo, d.Sal, e.Sal

FROM deleted AS d

JOIN employee AS e ON d.emp.id=e.emp_id WHERE UPDATED(Sal);

END;

 CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF

{INSERT | DELETE | UPDATE [OF column_name]}

ON view_name FOR EACH ROW

[REFERENCING OLD AS old_name NEW AS new_name]

[WHEN (condition)]

PL/SQL block;

 Defines, how the given change should be implemented on

underlying tables in the DB

 Views

 USER_TRIGGERS

 USER_SOURCE

 Views

 SYS.TRIGGERS

 SYS.TRIGGER_EVENTS

 There are three set operators defined in ANSI SQL

 UNION [ALL]

▪ SELECT … FROM … WHERE … UNION ALL

SELECT … FROM … WHERE … UNION ALL …

▪ If ALL option is not used, the result contains only unique rows, else results are simply concatenated and can contain duplicities

▪ Uniqueness of the UNION (and other operators as well) requires either ordering or hashing of operands, if it is possible, it is desirable to avoid unique UNION and use UNION ALL instead.

 INTERSECT

▪ (Unique) intersect of two results of SELECT statement

 EXCEPT (Oracle uses its proprietary operator MINUS)

▪ (Unique) difference of two results of SELECT statement

 Standard SQL contains support for recursive queries since version ANSI SQL-99 in WITH statement

 Oracle supports it

▪ Since version 9i release 2

 MS SQL supports it

▪ Since version MS SQL Server 2005

 Oracle SQL contains

also proprietary extension (with limited capabilities) using special clauses

START WITH and CONNECT BY

in the SELECT statement

 WITH

t1[(c11[,…])] AS (SELECT …)

[,t2[(c21[,…]) AS (SELECT …)] [, …]

SELECT … FROM t1, t2, …

 WITH construct defines sources t1, t2, …

used for data selection. Sources t1, t2, … are ad-hoc views existing and visible only In the associated

SELECT statement

 Source definitions can (but need not) reference themselves and previous definitions in WITH statement

 SELECT can (but need not) use defined sources

 Example:

Highest number of employees in one department

 WITH x AS (

SELECT DeptNo, COUNT(*) AS Cnt FROM Emp

GROUP BY DeptNo)

SELECT MAX(Cnt) AS MaxCnt

FROM x;

 Using WITH WITH x AS (

SELECT DeptNo,

COUNT(*) AS Cnt FROM Emp

GROUP BY DeptNo) SELECT MAX(x.Cnt) FROM x;

 Without WITH

SELECT MAX(x.Cnt) FROM (

SELECT DeptNo,

COUNT(*) AS Cnt FROM Emp

GROUP BY DeptNo

) x ;

 Using WITH

WITH x AS (

SELECT DeptNo, COUNT(*) AS Cnt FROM Emp

GROUP BY DeptNo)

SELECT a.DeptNo, b.DeptNo, a.Cnt FROM x AS a, x AS b

WHERE a.Cnt=b.Cnt

AND a.DeptNo<b.DeptNo ;

 Example: Table Emp with following structure:

EmpNo … Employee ID

EName … Name of the employee

Mgr … ID of Employee’s manager (direct supervisor) We would like to list all employees together with the degree of subordination (level in the company

hierarchy). Employees without supervisor have

degree (level) equal to one.

 Example:

WITH e(EName,Degree) AS (

-- all employees without supervisor (at level one)

SELECT x.EName, 1 AS Degree FROM Emp x

WHERE Mgr IS NULL UNION ALL -- recursive part

-- all employees at level k+1 (supervised by those at level k)

SELECT y.EName, e.Degree+1 AS Degree FROM Emp y JOIN e ON y.Mgr=e.EmpNo )

SELECT * FROM e;

 SELECT columns FROM table_name [WHERE

condition3]

START WITH condition1 CONNECT BY [NOCYCLE]

condition2 [ORDER BY …]

[ORDER SIBLINGS BY …]

 Rows complying the

condition1 in START WITH

are supposed to be rows at

level 1

 SELECT columns FROM table_name [WHERE

condition3]

START WITH condition1 CONNECT BY [NOCYCLE]

condition2 [ORDER BY …]

[ORDER SIBLINGS BY …]

 For each row found at level k are recursively searched

direct descendants at level k+1, that satisfy condition2 in CONNECT BY clause

 Condition2 has to deal with parent and descendant rows.

Values belonging to parent row are preceded by keyword PRIOR

 NOCYCLE stops recursion if the node should appear

second time in the same path. Without it the

reappearance causes error.

 SELECT columns FROM table_name [WHERE

condition3]

START WITH condition1 CONNECT BY [NOCYCLE]

condition2 [ORDER BY …]

[ORDER SIBLINGS BY …]

 At the end all rows not satisfying condition3 in

WHERE clause are eliminated (descendants were already processed and so they remain in the result even if their

predecessor is removed

afterwards)

 SELECT columns FROM table_name [WHERE

condition3]

START WITH condition1 CONNECT BY [NOCYCLE]

condition2 [ORDER BY …]

[ORDER SIBLINGS BY …]

 Without ORDER BY the order of rows corresponds to pre- order search.

 Each row contains a pseudo- column LEVEL, with the level in hierarchy.

 Order of descendants of one particular parent is not

defined by default. It can be specified by the

ORDER SIBLINGS BY clause

 Example:

SELECT EName, Level AS Degree FROM Emp e

START WITH Mgr IS NULL CONNECT BY

PRIOR EmpNo = Mgr;

 SELECT – with padding according to level

LPAD(‘ ‘,2*Level)||EName AS EName, …

 It is often necessary to return value where the expression used depends on some value

 Decoding of value using fixed codebook

▪ 1 ~ Monday … 7 ~ Sunday

▪ 0 ~ 0% VAT, 1 ~ 15% VAT, 2 ~ 21% VAT

▪ etc.

 It can be ineffective to create function for each such situation or define the table for those codebooks

and complicate queries by additional join (here the

set of values is fixes and doesn’t change or change

minimal in time)

 ANSI SQL-92 defines two forms of CASE expression

 a) simple CASE expression:

CASE expression

WHEN pattern1 THEN value1 WHEN pattern2 THEN value2

… [ELSE default]

END

 Returns value for first match expression=pattern _i ,

resp. default value, resp. NULL

 ANSI SQL-92 defines two forms of CASE expression

 b) searched CASE expression:

CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2

… [ELSE default]

END

 Return value for first match

condition _i = TRUE,

 Example:

SELECT Course, CASE Day

WHEN 1 THEN ’Monday’

WHEN 2 THEN ’Tuesday’

…

WHEN 7 THEN ’Sunday’

ELSE ’UNKNOWN DAY’

END AS Day_of_Week

FROM Schedule;

 Example: Sales split according to VAT type:

SELECT

CASE WHEN TaxGrp=0 THEN Price ELSE NULL END AS Price_00,

CASE WHEN TaxGrp=1 THEN Price*1.15 ELSE NULL END AS Price_15,

CASE WHEN TaxGrp=2 THEN Price*1.21 ELSE NULL END AS Price_21

FROM Sales;

 Oracle implements additional proprietary function DECODE

 DECODE(expression pattern1, value1, pattern2, value2[,

…][,

default])

 Corresponds to simple CASE expression

 Less universal (available only in SELECTs)

 Not portable

 Example:

SELECT Course, DECODE(Day

1, ’Monday’, 2, ’Tuesday’,

…

7, ’Sunday’

’UNKNOWN DAY’

) AS Day_of_Week FROM Schedule;

 Example:

SELECT Course, CASE Day

WHEN 1 THEN ’Monday’

WHEN 2 THEN ’Tuesday’

…

WHEN 7 THEN ’Sunday’

ELSE ’UNKNOWN DAY’

END AS Day_of_Week

FROM Schedule;