Scribd
Upload
22 views39 pages

Lecture 1.3 Triggers and Functions

The document discusses database concepts like procedures, functions, triggers and packages. It provides syntax for creating functions and triggers in Oracle database. It also explains the difference between procedures and functions. Examples are given to demonstrate functions and triggers.

Uploaded by

komal yadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
22 views39 pages

Lecture 1.3 Triggers and Functions

The document discusses database concepts like procedures, functions, triggers and packages. It provides syntax for creating functions and triggers in Oracle database. It also explains the difference between procedures and functions. Examples are given to demonstrate functions and triggers.

Uploaded by

komal yadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 39

Department of Computer Science and Engineering (CSE)

Database
Management
System

Course Outcome Will be covered in


CO Title Level this lecture
Numbe
r
CO1 To perceive the significance and Remember
implementation of a commercial
relational database system (Oracle)
by writing SQL using the system.
CO2 To understand the relational database Understand
theory, and be able to write
relational algebra expressions for
queries

CO3 To identify the basic issues of Analysis and


transaction processing and application
concurrency control and find out its
solutions.
2
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)

Contents of the Syllabus

UNIT-I [10h]
Overview of Databases: Database concepts, DBMS, Data Base System Architecture (Three
Level ANSI-SPARC Architecture), Advantages and Disadvantages of DBMS, Data Independence,
DBA and Responsibilities of DBA, Relational Data Structure, Keys, Relations, Attributes, Schema and
Instances, Referential integrity, Entity integrity.
Data Models: Relational Model, Network Model, Hierarchical Model, ER Model: Design,
issues, Mapping constraints, ER diagram, Comparison of Models.

Relational Algebra & Relational Calculus: Introduction, Syntax, Semantics, Additional


operators, Grouping and Ungrouping, Relational comparisons, Tuple Calculus, Domain Calculus,
Calculus Vs Algebra, Computational capabilities.

UNIT-II [10h]
Functional dependencies and Normalization: Functional dependencies, Decomposition, Full
Functional Dependency (FFD), Transitive Dependency (TD), Join Dependency (JD), Multi-valued
Dependency (MVD), Normal Forms (1NF, 2NF, 3NF, BCNF), De-normalization.
Database Security: Introduction, Threats, Counter Measures.
Control Structures: Introduction to conditional control, Iterative control and sequential control
statements, Cursors, Views.

3
University Institute of Engineering (UIE)
Department of Computer Science and Engineering (CSE)

Contents of the Syllabus

UNIT-III [10h]
Package, Procedures and Triggers: Parts of procedures, Parameter modes, Advantages of
procedures, Syntax for creating triggers, Types of triggers, package specification and package body,
developing a package, Bodiless package, Advantages of packages.
Transaction Management and Concurrency Control: Introduction to Transaction Processing,
Properties of Transactions, Serializability and Recoverability, Need for Concurrency Control, Locking
Techniques, Time Stamping Methods, Optimistic Techniques and Granularity of Data items.

Database Recovery of database: Introduction, Need for Recovery, Types of errors, Recovery
Techniques.

4
University Institute of Engineering (UIE)
Department of Computer and Communication Engineering (CCE)

(Package, Procedures and Triggers)

Package, Procedures and Triggers: Parts of procedures, Parameter modes,


Advantages of procedures, Syntax for creating triggers, Types of triggers, package
specification and package body, developing a package, Bodiless package,
Advantages of packages.

University Institute of Engineering (UIE) 5


Department of Computer and Communication Engineering (CCE)

Learning Objective

• Functions and its uses.


• Triggers
– Statement trigger
– Row trigger
– Enabling, disabling and dropping trigger

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

Learning Outcome
• Understanding the concept of procedures with example.
• Discuss the various parameters and its types.
• Understanding the use of functions in Data Base Management
System.
• Discuss the concepts of Trigger and its various types.
• Discuss the concepts of packages and its advantages

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

FUNCTIONS
The syntax for creating a function is as follows:

CREATE [OR REPLACE] FUNCTION function_name


(parameter list)
RETURN datatype
IS
BEGIN
<body>
RETURN (return_value);
END;

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

Example
CREATE OR REPLACE FUNCTION show_description
(i_course_no number)
RETURN varchar2
AS v_description varchar2(50);
BEGIN
SELECT description
INTO v_description
FROM course
WHERE course_no = i_course_no;
RETURN v_description;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN('The Course is not in the database');
WHEN OTHERS
THEN
RETURN('Error in running show_description');
END;

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

Making Use Of Functions


In a anonymous block

SET SERVEROUTPUT ON
DECLARE
v_description VARCHAR2(50);
BEGIN
v_description := show_description(&sv_cnumber);
DBMS_OUTPUT.PUT_LINE(v_description);
END;

In a SQL statement

SELECT course_no, show_description(course_no)


FROM course;

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

Functions
• Functions are declared using the following syntax:

function <function-name> (param_spec1, …, param_speck)


returns <return_type>
[not] deterministic allow optimization if same output
for the same input (use RAND not deterministic )
Begin
-- execution code
end;

where param_spec is:


[in | out | in out] <param_name> <param_type>

– You need ADMIN privilege to create functions on mysql-user server

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

Example of Functions

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

Example of Functions

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

How is a stored procedure different from a function?

Stored Procedure Function


Stored Procedure will not The function always
return a value, but the returns a value.
procedure can return “0”
or n values.

Whereas, Procedures can Functions have only input


have output or input parameters for it.
parameters.
But the vice-versa is not You can call Functions
correct. As you can’t call can be from Procedure.
Procedures from a
Function.

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

TRIGGERS
A database trigger is a stored PL/SQL program unit associated with a
specific database table. ORACLE executes (fires) a database trigger
automatically when a given SQL operation (like INSERT, UPDATE or
DELETE) affects the table. Unlike a procedure, or a function, which must be
invoked explicitly, database triggers are invoked implicitly.

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

TRIGGERS
• Database triggers can be used to perform any of the following:
• Audit data modification
• Log events transparently
• Enforce complex business rules
• Derive column values automatically
• Implement complex security authorizations
• Maintain replicate tables

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

TRIGGERS
You can associate up to 12 database triggers with a given table. A database
trigger has three parts: a triggering event, an optional trigger constraint,
and a trigger action.

When an event occurs, a database trigger is fired, and an predefined PL/SQL


block will perform the necessary action.

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

Triggers Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} triggering_event ON table_name
[FOR EACH ROW]
[WHEN condition]
DECLARE
Declaration statements
BEGIN
Executable statements
EXCEPTION
Exception-handling statements
END;

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

TRIGGERS
• The trigger_name references the name of the trigger.
• BEFORE or AFTER specify when the trigger is fired (before or after
the triggering event).
• The triggering_event references a DML statement issued against the
table (e.g., INSERT, DELETE, UPDATE).
• The table_name is the name of the table associated with the trigger.
• The clause, FOR EACH ROW, specifies a trigger is a row trigger and
fires once for each modified row.
• A WHEN clause specifies the condition for a trigger to be fired.
• Bear in mind that if you drop a table, all the associated triggers for the
table are dropped as well.

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

TYPES OF TRIGGERS
• Triggers may be called BEFORE or AFTER the following events:
• INSERT, UPDATE and DELETE.
• The before/after options can be used to specify when the trigger body
should be fired with respect to the triggering statement. If the user
indicates a BEFORE option, then Oracle fires the trigger before
executing the triggering statement. On the other hand, if an AFTER is
used, Oracle fires the trigger after executing the triggering statement.

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

TYPES OF TRIGGERS
A trigger may be a ROW or STATEMENT type. If the statement FOR
EACH ROW is present in the CREATE TRIGGER clause of a trigger, the
trigger is a row trigger. A row trigger is fired for each row affected by an
triggering statement.
A statement trigger, however, is fired only once for the triggering statement,
regardless of the number of rows affected by the triggering statement

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

Example: statement trigger


CREATE OR REPLACE TRIGGER mytrig1 BEFORE DELETE OR
INSERT OR UPDATE ON employee
BEGIN
IF (TO_CHAR(SYSDATE, 'day') IN ('sat', 'sun')) OR
(TO_CHAR(SYSDATE,'hh:mi') NOT BETWEEN '08:30' AND '18:30')
THEN RAISE_APPLICATION_ERROR(-20500, 'table is secured');

END IF;
END;
/
The above example shows a trigger that limits the DML actions to the
employee table to weekdays from 8.30am to 6.30pm. If a user tries to
insert/update/delete a row in the EMPLOYEE table, a warning message will
be prompted.
University Institute of Engineering (UIE)
Department of Computer and Communication Engineering (CCE)

Example: ROW Trigger


CREATE OR REPLACE TRIGGER mytrig2
AFTER DELETE OR INSERT OR UPDATE ON employee
FOR EACH ROW
BEGIN
IF DELETING THEN
INSERT INTO xemployee (emp_ssn, emp_last_name,emp_first_name, deldate)
VALUES (:old.emp_ssn, :old.emp_last_name,:old.emp_first_name, sysdate);
ELSIF INSERTING THEN
INSERT INTO nemployee (emp_ssn, emp_last_name,emp_first_name, adddate)
VALUES (:new.emp_ssn, :new.emp_last_name,:new.emp_first_name, sysdate);
ELSE
INSERT INTO uemployee (emp_ssn, emp_address, up_date)
VALUES (:old.emp_ssn, :new.emp_address, sysdate);
END IF;
END;
/

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

Example: ROW Trigger


• The previous trigger is used to keep track of all the transactions
performed on the employee table. If any employee is deleted, a new
row containing the details of this employee is stored in a table called
xemployee. Similarly, if a new employee is inserted, a new row is
created in another table called nemployee, and so on.
• Note that we can specify the old and new values of an updated row by
prefixing the column names with the :OLD and :NEW qualifiers.

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

TYPES OF TRIGGERS
SQL> DELETE FROM employee WHERE emp_last_name = 'Joshi';
1 row deleted.
SQL> SELECT * FROM xemployee;

EMP_SSN EMP_LAST_NAME EMP_FIRST_NAME DELDATE


------------- ----------------------- -------------------------- -----------------
999333333 Joshi Dinesh 02-MAY-03

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

ENABLING, DISABLING, DROPPING


TRIGGERS
SQL>ALTER TRIGGER trigger_name DISABLE;
SQL>ALTER TABLE table_name DISABLE ALL TRIGGERS;

To enable a trigger, which is disabled, we can use the following syntax:


SQL>ALTER TABLE table_name ENABLE trigger_name;

All triggers can be enabled for a specific table by using the following
command
SQL> ALTER TABLE table_name ENABLE ALL TRIGGERS;
SQL> DROP TRIGGER trigger_name

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

Triggers
• Trigger: stored program that is executed by the DBMS
whenever a specified event occurs

• Associated with a table or view

• Three trigger types: BEFORE and AFTER

• Each type can be declared for INSERT, UPDATE, and/or


DELETE

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

• To monitor a database
and take a corrective
action when a

SQL
condition occurs
– Examples:

Triggers
• Charge $10
overdraft fee if the
balance of an
account after a
withdrawal
transaction is less
than $500
• Limit the salary
increase of an
employee to no
more than 5% raise

CREATE TRIGGERUniversity Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

• Provide complex default values


Uses of data constraints
• Enforce
Triggers
• Update views – not in MySQL
• Perform referential integrity actions

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL Triggers: An Example

• We want to create a trigger to update the total salary of a


department when a new employee is hired

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL Triggers: An Example


● Create a trigger to update the total salary of a department
when a new employee is hired:

● The keyword “new” refers to the new row inserted

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL Triggers: An Example

totalsalary increases by 90K

totalsalary did not change

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL Triggers: An Example

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL Triggers: An Example

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL Triggers: An Example

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL Triggers: An Example

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

SQL
Triggers • To list
all the triggers
you have
created:

mysql>
show triggers;

University Institute of Engineering (UIE)


Department of Computer Science and Engineering (CSE)

Triggers Vs Stored Procedures


Sr. No. Key Triggers Stored procedures
1 Basic trigger is a stored procedure Stored procedures are a
that runs automatically when pieces of the code in written
various events happen (eg in PL/SQL to do some
update, insert, delete) specific task

2 Running It can execute automatically It can be invoked explicitly by


Methodology based on the events the user

3 Parameter It can not take input as It can take input as a


parameter parameter
4 Transaction we can't use transaction We can use transaction
statements statements inside a trigger statements like begin
transaction, commit
transaction, and rollback
inside a stored procedure

5 Return Triggers can not return values Stored procedures can return
values

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

FAQ
• What is procedures ? Explain with the help of examples.
• What is formal and actual parameter?
• Explain Row trigger with the help of example.
• How we can enable, disable and drop trigger?
• What do you understand by packages and write its advantages.

University Institute of Engineering (UIE)


Department of Computer and Communication Engineering (CCE)

References
• https://docs.oracle.com/database/121/LNPLS/packages.htm
• https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/p
ackages.htm
• https://www.guru99.com/packages-pl-sql.html
• https://docs.oracle.com/cd/B19306_01/server.102/b14200/st
atements_6009.htm
• http://searchoracle.techtarget.com/definition/stored-procedur
e
• https://www.w3resource.com/sql/sql-procedure.php
• https://docs.oracle.com/cd/B19306_01/server.102/b14220/tri
ggers.htm
• https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN7
3/ch15.htm
• https://docs.microsoft.com/en-us/sql/t-sql/statements/create-
trigger-transact-sql
University Institute of Engineering (UIE)

You might also like