Assertions(新言) An assertion is a predicate expressing a condition that we wish the database always to satisfy An assertion in SQL takes the form create assertion <assertion-name> check <predicate> When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion This testing may introduce a significant amount of overhead; hence assertions should be used with great care Asserting for all X, P(X is achieved in a round-about fashion using not exists X such that not P(X) Database System Concepts 6.11 OSilberschatz. Korth and Sudarshan
Database System Concepts 6.11 ©Silberschatz, Korth and Sudarshan Assertions（断言） An assertion is a predicate expressing a condition that we wish the database always to satisfy. An assertion in SQL takes the form create assertion <assertion-name> check <predicate> When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion This testing may introduce a significant amount of overhead; hence assertions should be used with great care. Asserting for all X, P(X) is achieved in a round-about fashion using not exists X such that not P(X)
Assertion Example The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch create assertion sum-constraint check (not exists(select* from branch where(select sum(amount) from loan where /oan branch-name s branch branch-name) >=(select sum(amount) from account where loan branch-name branch branch-name))) 标 Database System Concepts 6.12 OSilberschatz. Korth and Sudarshan
Database System Concepts 6.12 ©Silberschatz, Korth and Sudarshan Assertion Example The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch. create assertion sum-constraint check (not exists (select * from branch where (select sum(amount) from loan where loan.branch-name = branch.branch-name) >= (select sum(amount) from account where loan.branch-name = branch.branch-name)))
Assertion Example Every loan has at least one borrower who maintains an account with a minimum balance or $1000.00 create assertion balance-constraint check (not exists( select from loan where not exists select from borrower, depositor, account where loan. oan-number s borrower / oan-number and borrower customer-name depositor customer-name and depositor account-number account account-number and account balance >=1000)) Database System Concepts 6.13 @Silberschatz, Korth and Sudarshan
Database System Concepts 6.13 ©Silberschatz, Korth and Sudarshan Assertion Example Every loan has at least one borrower who maintains an account with a minimum balance or $1000.00 create assertion balance-constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan.loan-number = borrower.loan-number and borrower.customer-name = depositor.customer-name and depositor.account-number = account.account-number and account.balance >= 1000)))
Triggers A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database o design a trigger mechanism, we must Specify the conditions under which the trigger is to be executed Specify the actions to be taken when the trigger executes Event-condition-action model Triggers introduced to SQL standard in SQL: 1999, but supported even earlier using non-standard syntax by most databases Database System Concepts 6.14 @Silberschatz, Korth and Sudarshan
Database System Concepts 6.14 ©Silberschatz, Korth and Sudarshan Triggers A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. To design a trigger mechanism, we must: Specify the conditions under which the trigger is to be executed. Specify the actions to be taken when the trigger executes. Event-condition-action model Triggers introduced to SQL standard in SQL:1999, but supported even earlier using non-standard syntax by most databases
Trigger Example Suppose that instead of allowing negative account balances, the bank deals with overdrafts by setting the account balance to zero creating a loan in the amount of the overdraft giving this loan a loan number identical to the account number of the overdrawn account The condition for executing the trigger is an update to the account relation that results in a negative balance value 标 Database System Concepts 6.15 OSilberschatz. Korth and Sudarshan
Database System Concepts 6.15 ©Silberschatz, Korth and Sudarshan Trigger Example Suppose that instead of allowing negative account balances, the bank deals with overdrafts by setting the account balance to zero creating a loan in the amount of the overdraft giving this loan a loan number identical to the account number of the overdrawn account The condition for executing the trigger is an update to the account relation that results in a negative balance value