1 / 9

Assertions and triggers

Assertions and triggers. 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') );

raven-dejesus
Download Presentation

Assertions and triggers

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related