Download
assertions and triggers n.
Skip this Video
Loading SlideShow in 5 Seconds..
Assertions and triggers PowerPoint Presentation
Download Presentation
Assertions and triggers

Assertions and triggers

121 Views Download Presentation
Download Presentation

Assertions and triggers

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Assertions and triggers Assertions and triggers

  2. Constraints • Attribute-based CHECK constraints create table … ( postcode number(4) check (postcode > 0) ); Checked at update to the table. • Tuple-based CHECK constraints create table … ( … check (gender = 'M' or name = 'Mary') ); Checked at update to the table. • Schema-level ASSERTIONS • Checked at any update to the tables in the assertion. • Not available in Oracle. Assertions and triggers

  3. Assertions • Syntax • create assertion name check (condition) • Has the ability to refer to all attributes in the database. • Examples from Garcia-Molina • Fig. 7.6 + fig. 7.7, page 338 • Example 7.14, page 339 Assertions and triggers

  4. CHECK doesn't give guarantees, 339 • CHECK conditions are checked when the attribute / row is updates. • Condition is not checked if other data are changed • CHECK conditions with sub-queries are not guaranteed to hold • Examples • Fig. 7.7, page 338 • Example, page 339 Assertions and triggers

  5. Assertions vs. triggers • Assertions must be checked at any change to the mentioned (in the assertion declaration) relations. • That takes a lot of time! • Oracle doesn't implement assertions!! • Triggers are executed at certain events specified by the database user • Not on every update. • That takes less time!! • Oracle implements triggers!! Assertions and triggers

  6. Triggers, syntax, 328 • create trigger triggerName … • Shah page 328 • Event based execution • BEFORE the event (insert / update) • AFTER the event • INSTEAD OF the event (works on views only) Assertions and triggers

  7. BEFORE triggers, 330 • Executed BEFORE the insert / update is executed by the DBMS • Example: • changeNullTrigger.sql • Better solution: Default value on the column • Shah page 330 • Hides the use of sequences (Oracle feature) from the application • Inserts the current date • You can refer to the pseudo variable :NEW Assertions and triggers

  8. AFTER trigger, 331 • Executed AFTER the insert / update is executed by the DBMS • You can refer to the pseudo variables :NEW and :OLD • Examples: • Shah fig. 14-16 + 14-17, page 332 • Employee_adu_triggerAfter.sql • Used to • Check the certain conditions are still true after the insert / update Assertions and triggers

  9. Instead-of triggers, 333 • Not part of the SQL3 standard, but common i commercial DBMS's • Ordinary triggers • Before / after the update • for updating base tables. • Instead-of triggers • executed instead of the update • for updating views • which would otherwise be non-updatable • Example Shah fig. 14-18, page 333 + 14-19, page 334 • Full_employee_insteadOfTrigger.sql Assertions and triggers