Triggers, Stored Functions and Stored Procedures

1
Triggers, Stored Functions and Stored Procedures
2
Embedding SQL In Program Code

• SQL can be embedded in triggers, stored
  procedures, and program code
• Functions may also be created and saved for later
  use

3
Functions

• You can create functions and store them for later
  recall
• Format
• CREATE FUNCTION ltfunc_namegt(ltparam_listgt) RETURN
  ltreturn_typegt AS ...
• When you call the function, use the SELECT
  command Table will be DUAL, an Oracle built-in
  dummy table

4
Triggers

• A trigger is a stored program that is executed by
  the DBMS whenever a specified event occurs on a
  specified table or view
• Three trigger types BEFORE, INSTEAD OF,
  and AFTER
• Each type can be declared for Insert, Update, and
  Delete
• Resulting in a total of nine trigger types
• Instead of used for Views
• Oracle supports all nine trigger types
• SQL Server supports six trigger types (only for
  INSTEAD OF and AFTER triggers)

5
Firing Triggers

• When a trigger is fired, the DBMS supplies
• Old and new values for the update
• New values for inserts
• Old values for deletions
• The way the values are supplied depends on the
  DBMS product

6
Uses for Triggers

• Trigger applications
• Provide default values
• Enforce data constraints
• Update views
• Perform referential integrity actions

7
Example of Asking Price default value which needs
to be greater than acquisition price.
8
(No Transcript)
9
Stored Procedures

• A stored procedure is a program that is stored
  within the database and is compiled when used
• In Oracle, it can be written in PL/SQL or Java
• In SQL Server, it can be written in TRANSACT-SQL
• Stored procedures can receive input parameters
  and they can return results
• Stored procedures can be called from
• Programs written in standard languages, e.g.,
  Java, C
• Scripting languages, e.g., JavaScript, VBScript
• SQL command prompt, e.g., SQLPlus, Query Analyzer

10
Stored Procedure Advantages

• Greater security as store procedures are always
  stored on the database server
• Decreased network traffic
• SQL can be optimized by the DBMS compiler
• Code sharing resulting in
• Less work
• Standardized processing
• Specialization among developers

11
(No Transcript)
12
Triggers vs. Stored Procedures
13
Get a listing of functions, triggers and stored
procedures

• select object_type, object_name
• from user_objects
• where object_type 'PROCEDURE'
• or object_type 'TRIGGER'  
• or object_type 'FUNCTION'

14
Get info from SQL

• If you would like to get a view about the
  parameters of the procedure, just useSQLgt desc
  ltprocedure namegtIf you would like to see the
  code for the procedure, then use (assuming you
  are logged in as the owner of the
  procedure)SQLgt SELECT Text FROM User_Source
  WHERE Name 'PROCEDURENAME' ORDER BY Line

15
Trigger, Stored Procedure Tutorial

• http//infolab.stanford.edu/ullman/fcdb/oracle/or
  -triggers.html
• http//adbc.kennesaw.edu