Transcript Slides on assertions and triggers

Assertions and triggers
Assertions and triggers
1
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
2
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
3
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
4
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
5
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
6
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
7
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
8
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
9