ch24-Enhanced Data Models for Advanced Applications

ch24-Enhanced Data Models for Advanced Applications -...

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

View Full Document Right Arrow Icon
Slide 24- 1 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Background image of page 1

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

View Full DocumentRight Arrow Icon
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Chapter 24 Enhanced Data Models for Advanced Applications
Background image of page 2
Slide 24- 3 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Outline Temporal databases Spatial and Multimedia databases Deductive databases
Background image of page 3

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

View Full DocumentRight Arrow Icon
Slide 24- 4 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Active Database Concepts and Triggers Generalized Model for Active Databases and Oracle Triggers Triggers are executed when a specified condition occurs during insert/delete/update Triggers are action that fire automatically based on these conditions
Background image of page 4
Slide 24- 5 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Event-Condition-Action (ECA) Model Generalized Model (contd.) Triggers follow an Event-condition-action (ECA) model Event : Database modification E.g., insert, delete, update), Condition : Any true/false expression Optional: If no condition is specified then condition is always true Action : Sequence of SQL statements that will be automatically executed
Background image of page 5

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

View Full DocumentRight Arrow Icon
Slide 24- 6 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Trigger Example Generalized Model (contd.) When a new employees is added to a department, modify the Total_sal of the Department to include the new employees salary Logically this means that we will CREATE a TRIGGER, let us call the trigger Total_sal1 This trigger will execute AFTER INSERT ON Employee table It will do the following FOR EACH ROW WHEN NEW.Dno is NOT NULL The trigger will UPDATE DEPARTMENT By SETting the new Total_sal to be the sum of old Total_sal and NEW. Salary WHERE the Dno matches the NEW.Dno; Condition
Background image of page 6
Slide 24- 7 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Example: Trigger Definition CREATE TRIGGER Total_sal1 AFTER INSERT ON Employee FOR EACH ROW WHEN (NEW.Dno is NOT NULL) UPDATE DEPARTMENT SET Total_sal = Total_sal + NEW. Salary WHERE Dno = NEW.Dno; The condition The action Can be FOR, AFTER, INSTEAD OF Can be INSERT, UPDATE, DELETE Can be CREATE or ALTER
Background image of page 7

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

View Full DocumentRight Arrow Icon
Slide 24- 8 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe CREATE or ALTER TRIGGER Generalized Model (contd.) CREATE TRIGGER <name> Creates a trigger ALTER TRIGGER <name> Alters a trigger (assuming one exists) CREATE OR ALTER TRIGGER <name> Creates a trigger if one does not exist Alters a trigger if one does exist Works in both cases, whether a trigger exists or not
Background image of page 8
Slide 24- 9 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Conditions Generalized Model (contd.) AFTER Executes after the event BEFORE Executes before the event INSTEAD OF Executes instead of the event Note that event does not execute in this case E.g., used for modifying views
Background image of page 9

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

View Full DocumentRight Arrow Icon
Slide 24- 10 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Row-Level versus Statement-level Generalized Model (contd.) Triggers can be
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.

Page1 / 60

ch24-Enhanced Data Models for Advanced Applications -...

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