Database-integrity restrictions in triggers and Domains

Database-integrity restrictions in triggers and Domains
Domain integrity restrictions

SQL supports the concept of DOMAIN, and can use the CREATE DOMAIN statement to CREATE a DOMAIN and the integrity constraints that the DOMAIN should meet. [Example 14] CREATE a Gender DOMAIN and declare the VALUE range of the Gender DOMAIN: create domain GenderDomain CHAR (2) CHECK (value in ('male', 'female ')); in this way, the description of Ssex in [Example 10] can be rewritten to Ssex GenderDomain [Example 15] to CREATE a GenderDomain and name the create domain GenderDomain CHAR (2) constraint gd check (value in ('male', 'female '));

[Example 16] Delete the restriction condition GD for the domain GenderDomain. Alter domain GenderDomain drop constraint gd; [Example 17] adds the restriction GDD on the DOMAIN GenderDomain. Alter domain GenderDomain add constraint gdd check (value in ('1', '0'); through [Example 16] and [Example 17], change the value range of gender from ('male', 'female ') to ('1', '0 ')

Trigger)

Trigger is a special event-driven process defined by the user in a relational table.
Automatically activated by the server
It can perform more complex checks and operations and has more precise and powerful data control capabilities.

Define a trigger

Create trigger syntax format: create trigger <TRIGGER Name> {BEFORE | AFTER} <TRIGGER event> ON <Table Name> for each {ROW | STATEMENT} [WHEN <TRIGGER condition>] <TRIGGER action body>

Syntax for defining a trigger:
1. Creator: Table owner
2. Trigger name
3. Table Name: target table of the trigger
4. trigger events: INSERT, DELETE, and UPDATE
5. Trigger type
ROW-Level Trigger (for each row)
STATEMENT-Level Trigger (for each statement)

For example, assume that an after update trigger is created on the TEACHER table in [Example 11. If the table TEACHER has 1000 rows, run the following statement: update teacher set Deptno = 5; if the trigger is a statement-Level Trigger, after the statement is executed, trigger occurs only once. If it is a row-level trigger, the trigger will be executed for 1000 times.

Trigger Condition
The trigger condition is true.
Omitting the WHEN trigger Condition

Trigger action body
The trigger action body can be an anonymous PL/SQL process block.
You can also call a created stored procedure.

[Example 18] define a BEFORE row-Level Trigger and define the integrity rule for the instructor table Teacher: "The Professor's salary shall not be less than 4000 yuan. If the salary is less than 4000 yuan, it will be automatically changed to 4000 yuan ". Create trigger Insert_Or_Update_Sal before insert or update on Teacher/* TRIGGER event is an insert or update operation */for each row/* ROW-Level TRIGGER */as begin/* defines the TRIGGER action body, is a PL/SQL process block */IF (new. job = 'Professor ') AND (new. sal <4000) THEN new. sal: = 4000; end if; END;

[Example 19] define an AFTER row-level trigger, when the salary of the instructor TABLE changes, a corresponding record is automatically added to the salary change TABLE Sal_log. First, the salary change TABLE Sal_log create table Sal_log (Eno NUMERIC (4) is created) references teacher (eno), Sal NUMERIC (7,2), Username char (10), Date TIMESTAMP );

[Example 19] (continued) create trigger Insert_Sal after insert on Teacher/* the TRIGGER event is INSERT */for each row as begin insert into Sal_log VALUES (new. eno, new. sal, CURRENT_USER, CURRENT_TIMESTAMP); END;

[Example 19] (continued) create trigger Update_Sal after update on Teacher/* the TRIGGER event is UPDATE */for each row as begin if (new. sal <> old. sal) then insert into Sal_log VALUES (new. eno, new. sal, CURRENT_USER, CURRENT_TIMESTAMP); end if; END;

Activate a trigger

Trigger execution is activated by the trigger event and automatically executed by the database server.
Multiple triggers may be defined on a data table.
When multiple triggers on the same table are activated, the following execution sequence is followed:
(1) execute the BEFORE trigger on the table;
(2) activate the SQL statement of the trigger;
(3) execute the AFTER trigger on the table.

[Example 20] execute an SQL statement to modify the salary of a teacher and activate the trigger defined above. UPDATE Teacher SET Sal = 800 WHERE Ename = 'chen ping'; execution sequence: Execute the trigger Insert_Or_Update_Sal to execute the SQL statement "UPDATE Teacher SET Sal = 800 WHERE Ename = 'chen ping '; "execute the trigger Insert_Sal; execute the trigger Update_Sal

Delete trigger

SQL syntax for deleting a trigger:
Drop trigger <TRIGGER Name> ON <Table Name>;
A trigger must be a created trigger and can only be deleted by users with corresponding permissions.

[Example 21] Delete the TRIGGER Insert_Sal drop trigger Insert_Sal ON Teacher in the instructor table;

The integrity of the database is to ensure that the data stored in the database is correct.

RDBMS integrity Implementation Mechanism
Integrity Constraint definition Mechanism
Integrity check mechanism
Actions taken by RDBMS when integrity constraints are violated