Oracle

ORACLE RÈN LUYỆN CƠ BẢN
Date：2002/06/26
O
Introduction
NỘI DUNG MÔN HỌC
Oracle Database Architecture 0.5 hrs
Gíơi Thiệu về Ứng Dụng Trong SQL 3.5 hrs
Gíơi Thiệu về Ứng Dụng TrongPL/SQL 4 hrs

I
Database Architecture
Instance







Overview




Database
SGA



Redo log
buffer
Data buffer
cache
PMON
DBW0
SMON
LGWR
CKPT
Others
User
process
Server
process
PGA
Control
files
Data files
Redo log
files
Archived
log files
Parameter
file
Password
file
Oracle Instance
Background processes
An Oracle instance:
Is a means to access an Oracle database
Always opens one and only one database
Memory structures
Instance







SGA



PMON
DBW0
SMON
LGWR
CKPT
Others
Oracle Database file
Password
file
Parameter
file
Archived
log files
Control
files
Data files
Redo log
files
Database
Other Key Physical Structures
Database
Password
file
Parameter
file
Archived
log files
Database storage hierarchy
Database
Logical
Physical
Tablespace
Data file
OS block
Oracle
block
Segment
Extent
Connecting to an Instance
Oracle server
User
Server
Application server
Sessions
mỗI một kết nối，một Session
khi user login tới oracle server sẽ sinh ra một Process，Process này gọI là server process
mỗI một Server Process xử lý 1 user process
User logout hoặc disconnect，thì Session END
MTS (Multithreaded server)
một Server Process sẽ đồng thời xử lý được nhiều User Process
II
Giới thiệu về ngôn ngữ SQL
SQL Statement
Select (Data retrieval)
DML (Data Manipulation Language)
Insert, Update, Delete
DDL (Data Definition Language)
Create, Alter, Drop, Rename, Truncate
Transaction Control
Commit, Rollback, Save Point
DCL (Data Control Language)
Grant, Revoke

The Basic Query block
Distinct Không cho trùng lắp dữ liệu
* Hiện thị tất cả các column
Alias Đưa ra tên khác của column
SELECT [distinct] {*, column [alias],……}
FROM table
Select all column all rows
Câu lệnh SQL đơn giản nhất cần bao gồm 2 mệnh đề:
．SELECT clause
* is select all data
．FROM clause
SELECT *
FROM s_dept;
Selecting specific column
hiện ra những Column cần thiết
sử dụng ” , “để ngăn cách Column
hiện thị thứ tự sắp xếp Column theo ý muốn của bạn

SELECT dept_id, last_name, manager_id
FROM s_emp;
sử dụng các phép tính
Các phép tính có thể dùng cho NUMBER and DATE datatype
Add +
Subtract -
Multiply *
Divide /
SELECT last_name, 12 * salary +100, commission_pct
FROM s_emp;
Column Aliases
SELECT last_name, salary ,
salary, 12 * salary +100 AS ANNUAL_SALARY
FROM s_emp;
SELECT last_name, salary ,
salary, 12 * salary +100 “ ANNUAL SALARY”
FROM s_emp;
SELECT last_name, salary ,
salary, 12 * salary +100 ANNUAL_SALARY
FROM s_emp;
Concatenation Operator
SELECT first_name||last_name
FROM s_emp;
dùng”||” để nốI chuổi
Có thể nối trường hoặc ký hiệu vớI các trường khác
kết quả nốI chuôi là một chuôi ký tự

SELECT first_name||’ ‘|| last_name
||’,’|| title “Employees”
FROM s_emp;
Managing Null Values
NULL là một unavailable, unassigned, unknown, or giá trị inapplicable 。
Nó với giá trị zero hoặc space không giống nhau。
Khi tính toán nếu có giá trị null thì kết quả sẽ là null NULL
NVL Function
để chuyển giá trị NULL thành giá trị cần thiết。
Datatype chuyển đổI được có date, character, and number
Datatypes must match
NVL(start_date,’01-JAN-95’)
NVL(title, ‘No Title Yes’)
NVL (salary, 1000)
Limiting Selected Rows
dùng ORDER BY để sắp xếp
Dùng WHERE để truy tìm theo điều kiện
The ORDER BY Clause
ASC tăng dần (Default)
DESC giảm dần
Sau ORDER BY có thể dùng aliases
SELECT last_name EMPLOYEE, dept_id, start_date
FROM s_emp
ORDER BY EMPLOYEE DESC
Sorting by Multiple Columns
Dùng vị trí để sắp xếp
SELECT last_name , salary * 12
FROM s_emp
ORDER BY 2;
Có thể cùng 1 lúc dùng nhiều điều kiện sắp xếp
Order by có thể có các trường mà không select của bảng
SELECT last_name , dept_id , salary * 12
FROM s_emp
ORDER BY dept_id, salary DESC ,first_name ;
Comparison and Logical Operators
Logical comparison operators
= > >= < <=
SQL comparison operators
BETWEEN … AND …
IN (list)
LIKE
IS NULL
Logical operators
AND
OR
NOT
Negating Expressions
Có thể bỏ qua những giá trị đã biết mà không muôn nó hiện thị
Logical Operators
!= <> ^=
SQL Operators
NOT BETWEEN
NOT IN
NOT LIKE
IS NOT NULL
BETWEEN and IN SQL Operators
SELECT first_name, last_name, start_date
FROM s_emp
WHERE start_date BETWEEN ’09-MAY-91’ AND
’17-JUN-91’ ;
SELECT id, name, region_id
FROM s_dept
WHERE region_id IN (1,3);
LIKE SQL Operator
điều kiện truy xuất có thể bao gồm chuỗI or số
“%” một hoặc nhiều ký tự
“_” một ký tự
SELECT first_name, last_name, start_date
FROM s_emp
WHERE start_date LIKE ’%91’
SELECT first_name, last_name, start_date
FROM s_emp
WHERE last_name LIKE ’_a%’
Is NULL Operator
SELECT id, name,credit_rating
FROM s_customer
WHERE sales_rep_id = NULL ;
No rows selected
SELECT id, name,credit_rating
FROM s_customer
WHERE sales_rep_id = ‘ ‘ ;
No rows selected
SELECT id, name,credit_rating
FROM s_customer
WHERE sales_rep_id IS NULL
Character Functions
LOWER chuyển thành viết thường
UPPER chuyển thành viết hoa
INITCAP chuyển ký tự đầu thành viết hoa
CONCAT nối 2 chuỗi
SUBSTR lấy ra chuỗI con theo vi trí
LENGTH chiều dài chuỗi
Character Functions
CONCAT(‘Good’,’String’) →GoodString
SUBSTR(‘String’,1,3) →Str
LENGTH(‘String’) → 6
Number Functions
ROUND làm tròn số lẽ，dưới 4 bỏ qua
TRUNC làm tròn số lẽ，vô điều kiện
MOD số dư
Number Function
ROUND(45.923,2) → 45.92
ROUND(45.923,0) → 46
ROUND(45.923,-1) → 50
TRUNC(45.923,2) → 45.92
TRUNC(45.923) → 45
TRUNC(45.923,-1) → 40
MOD(1600,300) → 100
Oracle Date Formate
nội dung ngày của Oracle gồm:
thế kỵ，năm，tháng，ngày，giờ，phút，giây
kiểu hiện thị mặc định DD-MON-YY
Có thể dùng SYSDATE function để hiện thị ngày hiện hành。
SYSDATE có thể lấy giá trị ngày từ DUAL Table 。
Date Functions
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAT
ROUND
TRUNC
Date Functions
MONTHS_BETWEEN(’01_SEP_95’,’11_JAN_94’)
→1.9774194
ADD_MONTHS(’11-JAN-94’,6) →’11-JUL-94’
NEXT_DAY(’01-SEP-95’,’FRIDAY’) →’08-SEP-95’
LAST_DAY9(01-SEP-95) →’30-SEP-95’
ROUND(’25-MAY-95’,’MONTH’) →01-JUN-95
ROUND(’25-MAY-95’,’YEAR’) →01-JAN-95
TRUNC(’25-MAY-95’,’MONTH’) →01-JAN-95
TRUNC(’25-MAY-95’,’YEAR’) → 01-JAN-95
Conversion Functions
TO_CHR chuyển số or ngày thành chuỗi
TO_NUMBER chuyển chu6ỗI thành số
TO_DATE chuyển chuỗI thành ngày
TO_CHAR Function with Number Formats
TO_CHAR Function with Date Formats
TO_DATE Functions
What is Join?
từ trên một table để truy xuất dữ liệu。
Rows để kết vớI cùng một giá trị ，thường dùng giá trị của primary key (PK),và Foreign key (FK) 。
Phương pháp Join
Equijoin
Non-equijoin
Outer join
Self join
Relations Between Tables
Equijoin:example
Equijoin:Example
Non-equijoin:example
Outer Joins
Outer Joins:Syntax
Outer Joins:Example
Self Joins
Self Join : Example
Group by :Example
Subquery Guidelines
Subqueries có 2 loại:
Single row
Multiple row
Subquery phải dùng( ) để phân biệt
Subquery phảI viết ở bên phải
Subquery có thể sử dụng nhiều loạI SQL command
Subquery không thể có ORDER BY
Single Subquery
Multiple row Subquries
HAVING Clause with Nested Subqueries
Insert :syntax
INSERT INTO table [(column [,column…..])]
VALUES (value [,value……]);
Copying Rows from Another Table
UPDATE Syntax
UPDATE table
SET column = value [,column = value …]
[WHERE condition];
DELETE Syntax
DELETE [FROM[ table
[WHERE condition];
III
giớI thiệu về ứng dụng PL/SQL
Overview of PL/SQL

Block Structure for Anonymous PL/SQL Blocks
DECLARE (optional)
Define PL/SQL objects to be used within this block
BEGIN (mandatory)
Executable statements
EXCEPTION (optional)
What to do if the executable action causes an error condition
END; (mandatory )
Block Structure for PL/SQL - Subprograms
Header
IS
Declaration section
BEGIN
Executable section
EXCEPTION
Exception section
END;
Development Environments
SQL*Plus
Utilizes the PL/SQL engine in the Oracle Server
Procedure Builder/SQL Developer
Utilizes the PL/SQL engine in the client tool or in the Oracle Server
OtherS: TOAD
Developing Procedures Using SQL*Plus
Developing Procedures and Functions Using PL/SQL Developer
Creating Procedures
Overview of Procedures
A procedure is a named PL/SQL block that performs an action.
A procedure can be stored in the database, as a database object, for repeated execution.
Syntax for Creating Procedures

CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .
IS [AS]
PL/SQL Block;



Procedural Parameter Modes
Calling
environment
IN OUT
Must be specified
Passed into subprogram; returned to calling environment
Initialized variable

Must be a variable
OUT
Must be specified
Returned to calling environment


Uninitialized variable
Must be a variable
Parameter Modes for Formal Parameters
IN
Default
Value is
passed into subprogram

Formal parameter acts as a constant
Actual parameter
can be a literal, expression,
constant, or
initialized variable
IN Parameters: Example

SQL> CREATE OR REPLACE PROCEDURE raise_salary
2 (v_id in emp.empno%TYPE)
3 IS
4 BEGIN
5 UPDATE emp
6 SET sal = sal * 1.10
7 WHERE empno = v_id;
8 END raise_salary;
9 /
Procedure created.

SQL> EXECUTE raise_salary (7369)
PL/SQL procedure successfully completed.
v_id
7369
OUT Parameters: Example
Calling environment
QUERY_EMP procedure
7654
v_id
v_name
v_salary
v_ comm
MARTIN
1250
1400
OUT Parameters: Example
SQL> CREATE OR REPLACE PROCEDURE query_emp
1 (v_id IN emp.empno%TYPE,
2 v_name OUT emp.ename%TYPE,
3 v_salary OUT emp.sal%TYPE,
4 v_comm OUT emp.comm%TYPE)
5 IS
6 BEGIN
7 SELECT ename, sal, comm
8 INTO v_name, v_salary, v_comm
9 FROM emp
10 WHERE empno = v_id;
11 END query_emp;
12 /
OUT Parameters and SQL*Plus
SQL> START emp_query.sql
Procedure created.
SQL> VARIABLE g_name varchar2(15)
SQL> VARIABLE g_salary number
SQL> VARIABLE g_comm number
SQL> EXECUTE query_emp (7654, :g_name, :g_salary,
2 :g_comm)
PL/SQL procedure successfully completed.
SQL> PRINT g_name
G_NAME
---------------
MARTIN
PL/SQL> .CREATE CHAR g_name LENGTH 10
PL/SQL> .CREATE NUMBER g_salary PRECISION 4
PL/SQL> .CREATE NUMBER g_comm PRECISION 4
PL/SQL> exec query_emp (7654, :g_name, :g_sal,
+> :g_comm);
PL/SQL> TEXT_IO.PUT_LINE (:g_name || ` earns ` ||
+> TO_CHAR(:g_sal) || ` and a commission of `
+> || TO_CHAR(:g_comm));
MARTIN earns 1250 and a commission of 1400
OUT Parameters and Procedure Builder
IN OUT Parameters
SQL> CREATE OR REPLACE PROCEDURE format_phone
2 (v_phone_no IN OUT VARCHAR2)
3 IS
4 BEGIN
5 v_phone_no := `(` || SUBSTR(v_phone_no,1,3) ||
6 `)` || SUBSTR(v_phone_no,4,3) ||
7 `-` || SUBSTR(v_phone_no,7);
8 END format_phone;
9 /
Calling environment
FORMAT_PHONE procedure
v_phone_no
`(800)633-0575`
`(800)633-0575`
Invoking FORMAT_PHONE
from SQL*Plus
SQL>VARIABLE g_phone_no varchar2(15)

SQL> BEGIN :g_phone_no := `8006330575`; END;
2 /
PL/SQL procedure successfully completed.

SQL> EXECUTE format_phone (:g_phone_no)
PL/SQL procedure successfully completed.

SQL> PRINT g_phone_no
G_PHONE_NO
---------------
(800)633-0575
Methods for
Passing Parameters
Positional
Named
Combination
Passing Parameters: Example Procedure
SQL> CREATE OR REPLACE PROCEDURE add_dept
1 (v_name IN dept.dname%TYPE DEFAULT `unknown`,
2 v_loc IN dept.loc%TYPE DEFAULT `unknown`)
3 IS
4 BEGIN
5 INSERT INTO dept
6 VALUES (dept_deptno.NEXTVAL, v_name, v_loc);
7 END add_dept;
8 /
Examples of Passing Parameters
SQL> begin
2 add_dept;
3 add_dept ( `TRAINING`, `NEW YORK`);
4 add_dept ( v_loc => `DALLAS`, v_name =>
`EDUCATION`) ;
5 add_dept ( v_loc => `BOSTON`) ;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM dept;

DEPTNO DNAME LOC
------ -------------- -------------
... ... ...
41 unknown unknown
42 TRAINING NEW YORK
43 EDUCATION DALLAS
44 unknown BOSTON
Invoking a Procedure from an Anonymous PL/SQL Block
DECLARE
v_id NUMBER := 7900;
BEGIN
raise_salary(v_id); --invoke procedure
COMMIT;
...

END;
Invoking a Procedure from a Stored Procedure
SQL> CREATE OR REPLACE PROCEDURE process_emps
2 IS
3 CURSOR emp_cursor IS
4 SELECT empno
5 FROM emp;
6 BEGIN
7 FOR emp_rec IN emp_cursor LOOP
8 raise_salary(emp_rec.empno); --invoke procedure
9 END LOOP;
10 COMMIT;
11 END process_emps;
12 /
Removing Server-Side Procedures
Using SQL*Plus:
Syntax

Example
DROP PROCEDURE procedure_name
SQL> DROP PROCEDURE raise_salary;
Procedure dropped.
Creating Functions
Syntax for Creating Functions

CREATE [OR REPLACE] FUNCTION function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .
RETURN datatype
IS|AS
PL/SQL Block;



Creating a Stored Function Using SQL*Plus: Example
SQL> CREATE OR REPLACE FUNCTION get_sal
2 (v_id IN emp.empno%TYPE)
3 RETURN NUMBER
4 IS
5 v_salary emp.sal%TYPE :=0;
6 BEGIN
7 SELECT sal
8 INTO v_salary
9 FROM emp
10 WHERE empno = v_id;
11 RETURN (v_salary);
12 END get_sal;
13 /
Executing Functions in SQL*Plus: Example
Calling environment
GET_SAL function
v_id
7934
RETURN v_salary
Advantages of User-Defined Functions
in SQL Expressions
Extend SQL where activities are too complex, too awkward, or unavailable with SQL
Query efficiency: functions used in the WHERE clause can filter data
Manipulate character strings
Provide parallel query execution
Ex: select empno,tax(sal) from emp;
Locations to Call User-Defined Functions
Select list of a SELECT command
Condition of the WHERE and HAVING clauses
CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
VALUES clauses of the INSERT command
SET clause of the UPDATE command
Calling Functions from SQL Expressions: Restrictions
A user-defined function must be a stored function.
A user-defined function must be a ROW function, not a GROUP function.
A user-defined function only takes IN parameters, not OUT, or IN OUT.
Datatypes must be CHAR, DATE, or NUMBER, not PL/SQL types such as BOOLEAN, RECORD, or TABLE.
Return type must be an Oracle Server internal type.
Calling Functions from SQL Expressions: Restrictions
INSERT, UPDATE, or DELETE commands are not allowed.
Calls to subprograms that break the above restriction are not allowed.
Procedure or Function?
Calling
Environment
Calling
Environment
Comparing Procedures and Functions
Procedure
Execute as a PL/SQL statement
No RETURN datatype

Can return one or more values
Function
Invoke as part of an expression
Must contain a RETURN datatype
Must return a value
Creating Packages
Overview of Packages
Group logically related PL/SQL types, items, and subprograms
Consist of two parts:
Specification
Body
Cannot be called, parameterized, or nested
Allow Oracle8 to read multiple objects into memory at once
Developing a Package
Procedure A
declaration
Procedure B
definition
Package
specification
Package
body
1
2
3
4
2
Procedure A
definition
5
Developing a Package
Saving the text of the CREATE PACKAGE statement in two different text files facilitates later modifications to the package.
A package specification can exist without a package body, but a package body cannot exist without a package specification.
If you have incorporated a standalone procedure into a package, you should drop your standalone procedure.
CREATE [OR REPLACE] PACKAGE package_name
IS | AS
public type and item declarations
subprogram specifications
END package_name;
Creating the Package Specification
Syntax
Creating a Package Specification: Example
SQL>CREATE OR REPLACE PACKAGE comm_package IS
2 g_comm NUMBER := 10; --initialized to 10
3 PROCEDURE reset_comm
4 (v_comm IN NUMBER);
5 END comm_package;
6 /
Declaring a Global Variable
or a Public Procedure
SQL> EXECUTE comm_package.g_comm := 5
SQL> EXECUTE comm_package.reset_comm(8)
Creating the Package Body
Syntax
CREATE [OR REPLACE] PACKAGE BODY package_name
IS | AS
private type and item declarations
subprogram bodies
END package_name;
G_COMM
RESET_COMM
procedure declaration
VALIDATE_COMM
function definition
Package
specification
Package
body
1
2
3
2
RESET_COMM
procedure definition
COMM_PACKAGE package
Public and Private Constructs
Creating a Package Body: Example
SQL>CREATE OR REPLACE PACKAGE BODY comm_package IS
2 FUNCTION validate_comm
3 (v_comm IN NUMBER) RETURN BOOLEAN
4 IS
5 v_max_comm NUMBER;
6 BEGIN
7 SELECT MAX(comm)
8 INTO v_max_comm
9 FROM emp;
10 IF v_comm > v_max_comm THEN RETURN(FALSE);
11 ELSE RETURN(TRUE);
12 END IF;
13 END validate_comm;


SQL>PROCEDURE reset_comm
15 (v_comm IN NUMBER)
16 IS
17 v_valid BOOLEAN;
18 BEGIN
19 v_valid := validate_comm(v_comm);
20 IF v_valid = TRUE THEN
21 g_comm := v_comm;
22 ELSE
23 RAISE_APPLICATION_ERROR
24 (-20210,`Invalid commission`);
25 END IF;
26 END comm_package;
27 /


Creating a Package Body: Example
Developing Packages: Guidelines
Keep packages as general as possible.
Define the package specification before the body.
The package specification should only contain public constructs.
The package specification should contain as few constructs as possible.
Invoking Package Constructs
Example 1: Invoke a function from a procedure within the same package.
CREATE OR REPLACE PACKAGE BODY comm_package IS
. . .
PROCEDURE reset_comm(v_comm IN NUMBER)
IS
v_valid BOOLEAN;
BEGIN
v_valid := validate_comm(v_comm);
IF v_valid = TRUE THEN
g_comm := v_comm;
ELSE
RAISE_APPLICATION_ERROR (-20210, `Invalid comm`);
END IF;
END reset_comm;
END comm_package;
Example 2: Invoke a package procedure from SQL*Plus.

Example 3: Invoke a package procedure in a different schema.

Example 4: Invoke a package procedure in a remote database.
Invoking Package Constructs
SQL> EXECUTE comm_package.reset_comm(1500);
SQL> EXECUTE scott.comm_package.reset_comm(1500);
SQL> EXECUTE comm_package.reset_comm@ny (1500);
Persistent State of a Package Cursor
SQL> CREATE OR REPLACE PACKAGE pack_cur
2 IS
3 CURSOR c1 IS SELECT empno FROM emp
4 ORDER BY empno desc;
5 PROCEDURE proc1_3rows;
6 PROCEDURE proc4_6rows;
7 END pack_cur;
8 /
Example
SQL> CREATE OR REPLACE PACKAGE BODY pack_cur
2 IS
3 v_empno NUMBER;
4 PROCEDURE proc1_3rows IS
5 BEGIN OPEN c1;
6 LOOP FETCH c1 INTO v_empno;
7 DBMS_OUTPUT.PUT_LINE(`Id :` ||(v_empno));
8 EXIT WHEN c1%ROWCOUNT >= 3;
9 END LOOP;
10 END proc1_3rows;
11 PROCEDURE proc4_6rows IS
12 BEGIN
13 LOOP FETCH c1 INTO v_empno;
14 DBMS_OUTPUT.PUT_LINE(`Id :` ||(v_empno));
15 EXIT WHEN c1%ROWCOUNT >= 6;
16 END LOOP;
17 CLOSE c1;
18 END proc4_6rows;
19 END pack_cur;
20 /
Persistent State of a Package Cursor
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE pack_cur.proc1_3rows;
Id : 7934
Id : 7902
Id : 7900
SQL> EXECUTE pack_cur.proc4_6rows;
Id : 7876
Id : 7844
Id : 7839
Persistent State of a Package Cursor
CREATE OR REPLACE PACKAGE emp_package IS
TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE read_emp_table(emp_table OUT
emp_table_type);
END emp_package;


CREATE OR REPLACE PACKAGE BODY emp_package IS
PROCEDURE read_emp_table(emp_table OUT
emp_table_type)
IS
i BINARY_INTEGER:=0;
BEGIN
FOR emp_record IN (SELECT * FROM emp) LOOP
emp_table(i):=emp_record;
I:=I+1;
END LOOP;
END;
END emp_package;

Persistent State of Package PL/SQL Tables and Records
Removing Packages
DROP PACKAGE package_name
To remove the package specification and the body:
To remove the package body:
DROP PACKAGE BODY package_name
Summary
Improve organization, management, security, and performance.
Group related procedures and functions together.
Change a package body without affecting a package specification.
Grant security access to the entire package.
Creating Database Triggers

Objectives
After completing this lesson, you should be able to do the following:
Describe database triggers and their use
Create database triggers
Describe database trigger firing rules
Remove database triggers
Creating Triggers
Trigger timing: BEFORE or AFTER
Triggering event: INSERT or UPDATE or DELETE
Table name: On table
Trigger type: Row or statement
When clause: Restricting condition
Trigger body: DECLARE
BEGIN
END;
Trigger Components
Trigger Timing: When should the trigger fire?
BEFORE: The code in the trigger body will execute before the triggering DML event.
AFTER: The code in the trigger body will execute after the triggering DML event.
Trigger Components
Triggering Event:
What DML operation will cause the trigger to execute?
INSERT
UPDATE
DELETE
Any combination of the above
Trigger Components
Trigger Type:
How many times should the trigger body execute when the triggering event takes place?
Statement: The trigger body executes once for the triggering event. This is the default.
Row: The trigger body executes once for each row affected by the triggering event.
Trigger Components
Trigger Body:
What action should the trigger perform?
The trigger body is defined with an anonymous PL/SQL block.
[DECLARE]
BEGIN
[EXCEPTION]
END;
DEPTNO
10
20
30
40
DNAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
NEW YORK
DALLAS
CHICAGO
BOSTON

DEPT table
Firing Sequence of Database
Triggers on a Single Row
BEFORE row trigger
AFTER row trigger
Syntax for Creating
Statement Triggers

CREATE [OR REPLACE] TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
PL/SQL block;



Creating Statement Triggers Using Procedure Builder
Using Conditional Predicates
SQL>CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT OR UPDATE OR DELETE ON emp
3 BEGIN
4 IF (TO_CHAR (sysdate,`DY`) IN (`SAT`,`SUN`)) OR
5 (TO_CHAR (sysdate, `HH24`) NOT BETWEEN `08` AND `18`) THEN
6 IF DELETING THEN
7 RAISE_APPLICATION_ERROR (-20502,
8 `You may only delete from EMP during normal hours.`);
9 ELSIF INSERTING THEN
10 RAISE_APPLICATION_ERROR (-20500,
11 `You may only insert into EMP during normal hours.`);
12 ELSIF UPDATING (`SAL`) THEN
13 RAISE_APPLICATION_ERROR (-20503,
14 `You may only update SAL during normal hours.`);
15 ELSE
16 RAISE_APPLICATION_ERROR (-20504,
17 `You may only update EMP during normal hours.`);
18 END IF;
19 END IF;
20 END;
21 /
After Statement Trigger: Example
SQL>CREATE OR REPLACE TRIGGER check_salary_count
2 AFTER UPDATE OF sal ON emp
3 DECLARE
4 v_salary_changes NUMBER;
5 v_max_changes NUMBER;
6 BEGIN
7 SELECT upd, max_upd
8 INTO v_salary_changes, v_max_changes
9 FROM audit_table
10 WHERE user_name = user
11 AND table_name = `EMP`
12 AND column_name = `SAL`;
13 IF v_salary_changes > v_max_changes THEN
14 RAISE_APPLICATION_ERROR (-20501,
15 `You may only make a maximum of `||
16 TO_CHAR (v_max_changes) ||
17 ` changes to the SAL column`);
18 END IF;
19 END;
20 /
Creating a Row Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN condition]
PL/SQL block;



Creating Row Triggers Using Procedure Builder
SQL>CREATE OR REPLACE TRIGGER audit_emp
2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 IF DELETING THEN
6 UPDATE audit_table SET del = del + 1
7 WHERE user_name = user AND table_name = `EMP`
8 AND column_name IS NULL;
9 ELSIF INSERTING THEN
10 UPDATE audit_table SET ins = ins + 1
11 WHERE user_name = user AND table_name = `EMP`
12 AND column_name IS NULL;
13 ELSIF UPDATING (`SAL`) THEN
14 UPDATE audit_table SET upd = upd + 1
15 WHERE user_name = user AND table_name = `EMP`
16 AND column_name = `SAL`;
17 ELSE /* The data manipulation operation is a general UPDATE. */
18 UPDATE audit_table SET upd = upd + 1
19 WHERE user_name = user AND table_name = `EMP`
20 AND column_name IS NULL;
21 END IF;
22 END;
23 /
After Row Trigger: Example
SQL>CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp_values (user_name,
6 timestamp, id, old_last_name, new_last_name,
7 old_title, new_title, old_salary, new_salary)
8 VALUES (USER, SYSDATE, :old.empno, :old.ename,
9 :new.ename, :old.job, :new.job,
10 :old.sal, :new.sal);
11 END;
12 /
Using Old and New Qualifiers
SQL>CREATE OR REPLACE TRIGGER derive_commission_pct
2 BEFORE INSERT OR UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN (new.job = `SALESMAN`)
5 BEGIN
6 IF INSERTING THEN :new.comm := 0;
7 ELSE /* UPDATE of salary */
8 IF :old.comm IS NULL THEN
9 :new.comm :=0;
10 ELSE
11 :new.comm := :old.comm * (:new.sal/:old.sal);
12 END IF;
13 END IF;
14 END;
15 /
Restricting a Row Trigger
Differentiating Between Triggers and Stored Procedures
Triggers
Use CREATE TRIGGER
Data dictionary contains source and p-code
Implicitly invoked
COMMIT, SAVEPOINT, ROLLBACK not allowed
Procedure
Use CREATE PROCEDURE
Data dictionary contains source and p-code
Explicitly invoked
COMMIT, SAVEPOINT, ROLLBACK allowed
ALTER TRIGGER trigger_name DISABLE | ENABLE
Managing Triggers
Disable or Re-enable a database trigger
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS
Disable or Re-enable all triggers for a table
ALTER TRIGGER trigger_name COMPILE
Recompile a trigger for a table
Removing Triggers
To remove a trigger from the database, use the DROP TRIGGER syntax:
DROP TRIGGER trigger_name
Rules Governing Triggers
Rule 1: Do not change data in the primary key, foreign key, or unique key columns of a constraining table.
Rule 2: Do not read data from a mutating table.
Implementation of Triggers
Security
Auditing
Data integrity
Referential integrity
Table replication
Derived data
Event logging