advanced sql triggers assertions n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Advanced SQL: Triggers & Assertions PowerPoint Presentation
Download Presentation
Advanced SQL: Triggers & Assertions

Loading in 2 Seconds...

play fullscreen
1 / 36

Advanced SQL: Triggers & Assertions - PowerPoint PPT Presentation


  • 101 Views
  • Uploaded on

Advanced SQL: Triggers & Assertions. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. Today ’ s Roadmap. Views Triggers Assertions Cursors Stored Procedures. We covered these. To be covered today. Views ’ Recap. An SQL query that we register (store) inside the database

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Advanced SQL: Triggers & Assertions' - padma


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
advanced sql triggers assertions

Advanced SQL: Triggers & Assertions

Instructor: Mohamed Eltabakh

meltabakh@cs.wpi.edu

today s roadmap
Today’s Roadmap
  • Views
  • Triggers
  • Assertions
  • Cursors
  • Stored Procedures

We covered these

To be covered today

views recap
Views’ Recap
  • An SQL query that we register (store) inside the database
  • No Data are stored in the database (only the definition)
  • Any query can be a view

View name

View schema (pNumber, CNT)

CREATE VIEW ProfNumStudentsAS

SELECTpNumber, count(*) AS CNT

FROMStudent

GROUP BYpNumber;

views recap cont d
Views’ Recap (Cont’d)
  • Query a view as any regular table

CREATE VIEW ProfNumStudentsAS

SELECTpNumber, count(*) AS CNT

FROMStudent

GROUP BYpNumber;

Create the view

  • Query: Select the professor number who has more that 20 students

Select pNumber

From ProfNumStudents

Where CNT > 20;

Query the view

triggers introduction
Triggers: Introduction
  • The application constraints need to be captured inside the database
  • Some constraints can be captured by:
    • Primary Keys, Foreign Keys, Unique, Not NULL, and domain constraints

CREATE TABLE Students

(sid: CHAR(20),

name: CHAR(20) NOT NULL,

login: CHAR(10),

age: INTEGER,

gpa: REAL Default 0,

Constraint pk Primary Key (sid),

Constraint u1 Unique (login),

Constraint gpaMax check (gpa <= 4.0) );

These constraints are defined in CREATE TABLE or ALTER TABLE

triggers introduction1
Triggers: Introduction
  • Some application constraints are complex
    • Need for assertions and triggers
  • Examples:
    • Sum of loans taken by a customer does not exceed 100,000
    • Student cannot take the same course after getting a pass grade in it
    • Age field is derived automatically from the Date-of-Birth field
trigger components
Trigger Components
  • Three components
    • Event: When this event happens, the trigger is activated
    • Condition (optional): If the condition is true, the trigger executes, otherwise skipped
    • Action: The actions performed by the trigger
  • Semantics
    • When the Event occurs and Condition is true, execute the Action

Lets see how to define these components

trigger events
Trigger: Events
  • Three event types
    • Insert
    • Update
    • Delete
  • Two triggering times
    • Before the event
    • After the event
  • Two granularities
    • Execute for each row
    • Execute for each statement
1 trigger event
1) Trigger: Event

Trigger name

  • Example

Create Trigger <name>

Before|After Insert|Update|Delete ON <tablename>

….

That is the event

Create Trigger ABC

Before Insert On Students

….

Create Trigger XYZ

After Update On Students

….

This trigger is activated when an insert statement is issued, but before the new record is inserted

This trigger is activated when an update statement is issued and after the update is executed

granularity of event
Granularity of Event
  • A single SQL statement may update, delete, or insert many records at the same time
    • E.g., Update student set gpa = gpa x 0.8;
  • Does the trigger execute for each updated or deleted record, or once for the entire statement ?
    • We define such granularity

Create Trigger <name>

Before| After Insert| Update| Delete

For Each Row | For Each Statement

….

This is the event

This is the granularity

example granularity of event
Example: Granularity of Event

Create Trigger XYZ

After Update ON <tablename>

For each statement

….

Create Trigger XYZ

Before Delete ON <tablename>

For each row

….

This trigger is activated once (per UPDATE statement) after all records are updated

This trigger is activated before deleting each record

2 trigger condition
2) Trigger: Condition
  • This component is optional
  • Trigger executes only if the condition is true

Create Trigger <name>

Before| After Insert| Update| Delete On <tableName>

For Each Row | For Each Statement

When <condition>

That is the condition

If the employee salary > 150,000 then some actions will be taken

Create Trigger EmpSal

After Insert or Update On Employee

For Each Row

When (new.salary >150,000)

3 trigger action
3) Trigger: Action
  • Action depends on what you want to do, e.g.:
    • Check certain values
    • Fill in some values
    • Inserts/deletes/updates other records
    • Check that some business constraints are satisfied
    • Commit (approve the transaction) or roll back (cancel the transaction)
  • In the action, you may want to reference:
    • The new values of inserted or updated records (:new)
    • The old values of deleted or updated records (:old)
trigger referencing values
Trigger: Referencing Values
  • In the action, you may want to reference:
    • The new values of inserted or updated records (:new)
    • The old values of deleted or updated records (:old)

Create Trigger EmpSal

After Insert or Update On Employee

For Each Row

When (new.salary >150,000)

Begin

if (:new.salary < 100,000) …

End;

Inside “When”, the “new” and “old” should not have “:”

Trigger body

Inside the trigger body, they should have “:”

trigger referencing values cont d
Trigger: Referencing Values (Cont’d)
  • Insert Event
    • Has only :new defined
  • Delete Event
    • Has only :old defined
  • Update Event
    • Has both :new and :old defined
  • Before triggering (for insert/update)
    • Can update the values in :new
    • Changing :old values does not make sense
  • After triggering
    • Should not change :new because the event is already done
example 1
Example 1

If the employee salary increased by more than 10%, make sure the ‘rank’ field is not empty and its value has changed, otherwise reject the update

If the trigger exists, then drop it first

Create or Replace Trigger EmpSal

Before Update On Employee

For Each Row

Begin

IF (:new.salary > (:old.salary * 1.1)) Then

IF (:new.rank is null or :new.rank = :old.rank) Then

RAISE_APPLICATION_ERROR(-20004, 'rank field not correct');

End IF;

End IF;

End;

/

Compare the old and new salaries

Make sure to have the “/” to run the command

example 2
Example 2

If the employee salary increased by more than 10%, then increment the rank field by 1.

In the case of Update event only, we can specify which columns

Create or Replace Trigger EmpSal

Before Update Of salary On Employee

For Each Row

Begin

IF (:new.salary > (:old.salary * 1.1)) Then

:new.rank:= :old.rank + 1;

End IF;

End;

/

We changed the new value of rank field

The assignment operator has “:”

example 3 using temp variable
Example 3: Using Temp Variable

If the newly inserted record in employee has null hireDate field, fill it in with the current date

Since we need to change values, then it must be “Before” event

Create Trigger EmpDate

Before Insert On Employee

For Each Row

Declare

temp date;

Begin

Select sysdate into temp from dual;

IF (:new.hireDate is null) Then

:new.hireDate := temp;

End IF;

End;

/

Declare section to define variables

Oracle way to select the current date

Updating the new value of hireDate before inserting it

example 4 maintenance of derived attributes
Example 4: Maintenance of Derived Attributes

Keep the bonus attribute in Employee table always 3% of the salary attribute

Indicate two events at the same time

Create Trigger EmpBonus

Before Insert Or Update On Employee

For Each Row

Begin

:new.bonus := :new.salary * 0.03;

End;

/

The bonus value is always computed automatically

combining multiple events in one trigger
Combining Multiple Events in One Trigger
  • If you combine multiple operations
    • Sometimes you need to know what is the current operation

Combine Insertand Update

Can do something different under each operation

Create Trigger EmpBonus

Before Insert Or Update On Employee

For Each Row

Begin

IF (inserting) Then … End IF;

IF (updating) Then … End IF;

End;

/

before vs after
Beforevs. After
  • Before Event
    • When checking certain conditions that may cause the operation to be cancelled
      • E.g., if the name is null, do not insert
    • When modifying values before the operation
      • E.g., if the date is null, put the current date
  • After Event
    • When taking other actions that will not affect the current operations
      • The insert in table X will cause an update in table Y

Before Insert Trigger:

:new.x := …. //Changing value x that will be inserted

After Insert Trigger:

:new.x := … //meaningless because the value is already inserted

row level vs statement level triggers
Row-Level vs. Statement-Level Triggers
  • Example: Update emp set salary = 1.1 * salary;
    • Changes many rows (records)
  • Row-level triggers
    • Check individual values and can update them
    • Have access to :new and :old vectors
  • Statement-level triggers
    • Do not have access to :new or :old vectors (only for row-level)
    • Execute once for the entire statement regardless how many records are affected
    • Used for verification before or after the statement
example 5 statement level trigger
Example 5: Statement-level Trigger

Store the count of employees having salary > 100,000 in table R

Indicate three events at the same time

Create Trigger EmpBonus

After Insert Or Update of salary Or Delete On Employee

For Each Statement

Begin

delete from R;

insert into R(cnt) Select count(*) from employee where salary > 100,000;

End;

/

Delete the existing record in R, and then insert the new count.

order of trigger firing
Order Of Trigger Firing

Loop over each affected record

After Trigger

(row-level)

Before Trigger

(row-level)

Before Trigger

(statement-level)

Event

(row-level)

After Trigger

(statement-level)

some other operations
Some Other Operations
  • Dropping Trigger
  • If creating trigger with errors

SQL> Drop Trigger <trigger name>;

SQL > Show errors;

It displays the compilation errors

key points in triggers
Key Points in Triggers
  • Must understand what type of trigger to create
    • Before or After
    • Under which operation: Insert, Update, or Delete
    • Row-level or Statement-level
  • :old and :new variables
    • Update  both are available
    • Insert  Only :new is available
    • Delete  Only :old is available
assertions1
Assertions
  • An expression that should be always true
  • When created, the expression must be true
  • DBMS checks the assertion after any change that may violate the expression

Must return True or False

example 11
Example 1

Sum of loans taken by a customer does not exceed 100,000

Must return True or False (not a relation)

Create Assertion SumLoansCheck

( 100,000 >= ALL

Select Sum(amount)

From borrower B , loan L

Where B.loan_number = L.loan_number

Group By customer_name );

example 21
Example 2

Number of accounts for each customer in a given branch is at most two

Create Assertion NumAccountsCheck

( 2 >= ALL

Select count(*)

From account A , depositor D

Where A.account_number = D.account_number

Group By customer_name, branch_name );

example 3
Example 3

Customer city is always not null

Create Assertion CityCheckCheck

( NOT EXISTS (

Select *

From customer

Where customer_city is null));

assertions vs triggers
Assertions vs. Triggers
  • Assertions do not modify the data, they only check certain conditions
  • Triggers are more powerful because the can check conditions and also modify the data
  • Assertions are not linked to specific tables in the database and not linked to specific events
  • Triggers are linked to specific tables and specific events
assertions vs triggers cont d
Assertions vs. Triggers (Cont’d)
  • All assertions can be implemented as triggers (one or more)
  • Not all triggers can be implemented as assertions
  • Oracle does not have assertions
example trigger vs assertion
Example: Trigger vs. Assertion

All new customers opening an account must have opening balance >= $100. However, once the account is opened their balance can fall below that amount.

Trigger Event: Before Insert

We need triggers, assertions cannot be used

Create Trigger OpeningBal

Before Insert On Customer

For Each Row

Begin

IF (:new.balance is null or :new.balance < 100) Then

RAISE_APPLICATION_ERROR(-20004, 'Balance should be >= $100');

End IF;

End;