ch8 - IntegrityandSecurity n n Triggers Authorization 1...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Integrity and Security n Triggers n Authorization 
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Motivating Example n A bookstore needs to order more copies  from publishers if a book's inventory  falls below a certain number n How to implement this in Database?
Background image of page 2
3 Database Triggers n Program units that are attached to a specific table (e.g., which table the trigger should be attached to in  previous example?) n Executed automatically in response to certain  modifications to the table: n INSERT n UPDATE n DELETE n Can define specific conditions that trigger will be fired  (e.g., what's the condition in previous example?)
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 How Trigger Works n First create a trigger, with the condition the trigger  will be fired and the action will be taken n Every time the table gets modified, the condition  will be checked automatically by the database  system n If the condition is satisfied, the action will be taken  automatically
Background image of page 4
5 Uses For Database Triggers n Enforce complex constraints, business rules n E.g., an account can not have negative balance n What actions can we take? n Audit the changes to the table n Trigger is similar to PL/SQL procedures except  that  triggers  cannot  accept parameters n Any overhead associated with trigger?
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
6 Two Types of Triggers n Statement level:  trigger fires once, regardless of how  many rows are updated n Typically for auditing purpose n The trigger program can see the whole table being modified n Row level :  trigger fires once for each row that is affected n For most other situations n The trigger program can see the current row being modified  (through two system variables: new and old), but NOT other rows n Why TWO variables?
Background image of page 6
7 Trigger Timing n Two types of trigger in terms of timing n Before: the trigger is executed before the modification  occurs in database n After: the trigger is executed after the modification n Execution order for multiple triggers? n So there are actually 4 types of triggers, what are  they?
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
8 Creating a Statement-Level  Trigger CREATE OR REPLACE TRIGGER trigger_name {BEFORE|AFTER} {INSERT or UPDATE or DELETE} ON table_name [declare Declare local variables here] BEGIN trigger body END; {} is must [] is optional X | Y means X or Y n Insert, delete, update are types of modifications that will fire the trigger,  Use OR if you want to fire trigger in multiple types  n Before means before trigger, after means after trigger
Background image of page 8
9 Example create table orders(  oid number primary key); create table orders_audit  ( id number, aud_date date, aud_user varchar(100), primary key (id)); create sequence aud_seq start with 1;
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
10 Example -- statement level trigger:  -- generate an audit record for any changes to  order table create or replace trigger audit_orders
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/28/2012 for the course IS 620 taught by Professor Chen,z during the Spring '08 term at UMBC.

Page1 / 51

ch8 - IntegrityandSecurity n n Triggers Authorization 1...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online