Database-triggers and integrity limits in a domain

Integrity limits in a domain

SQL supports the concept of domains and can be usedThe  create  domain statement establishes a domain and the integrity constraints that the domain should satisfy.           Create a gender domain and declare a range of values for the gender domain create  domain genderdomain CHAR(2) CHECK (VALUE  in (' Male ',' female '));In this way [example 10] The description of Ssex can be rewritten as Ssex genderdomain[Example 15] to establish a gender domain Genderdomain and to name the restrictions thereincreate  DOMAIN  Genderdomain char  (2 ) constraint  GD check  (value  in  ( ' male ' ,  ' female ' )); 

［例16］删除域GenderDomain的限制条件GD。           ALTER  DOMAIN  GenderDomain             DROP CONSTRAINT GD;［例17］在域GenderDomain上增加限制条件GDD。          ALTER  DOMAIN  GenderDomain           ADD CONSTRAINT GDD CHECK (VALUE IN ( ‘1‘，‘0‘) );

Trigger (Trigger)

A trigger (Trigger) is a class of event-driven special procedures that user-defined on a relational table
Automatically activated by the server
More sophisticated inspections and operations with finer and more powerful data control capabilities

Defining triggers

CREATE TRIGGER语法格式      CREATE TRIGGER <触发器名>         {BEFORE | AFTER} <触发事件> ON <表名>        FOR EACH  {ROW | STATEMENT}      ［WHEN <触发条件>］        <触发动作体>

Defines the syntax description for a trigger:
1. Creator: Owner of the table
2. Trigger Name
3. Table name: Target table for triggers
4. Trigger event: INSERT, DELETE, UPDATE
5. Trigger type
Row-level triggers (for each row)
Statement-level triggers (for each STATEMENT)

UPDATE触发器。如果表TEACHER有1000行，执行如下语句：    UPDATE TEACHER SET Deptno=5;

1. Trigger conditions
   Trigger condition is true
   Omit when trigger condition

2. Trigger Action Body
   The triggering action body can be an anonymous PL/SQL process block
   can also be a call to a stored procedure that has already been created

[Example 18] define a before row-level trigger to define the integrity rules for teacher table teacher "The professor's salary should not be less than 4000 yuan, if less than 4000 yuan, automatically changed to 4000 yuan."create  TRIGGER  Insert_or_update_sal before  insert  or  update  on  Teacher /* Trigger event is INSERT or UPDATE action */for  each  row /* row-level trigger */as  be GIN /* Define trigger action body, is Pl/sql  process block */if  (new. Job=) and  (new. Sal < 4000 ) then  new. Sal: =4000 ;                                END IF;         END;                               

［例19］定义AFTER行级触发器，当教师表Teacher的工资发生变化后就自动在工资变化表Sal_log中增加一条相应记录   首先建立工资变化表Sal_log    CREATE TABLE Sal_log        (Eno    NUMERIC(4)  references teacher(eno)，          Sal     NUMERIC(7，2)，          Username  char(10)，          Date   TIMESTAMP         )；

[例19]（续）CREATE TRIGGER Insert_Sal                       AFTER INSERT ON Teacher         /*触发事件是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/* Trigger event is update  */for< /span> 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;

activating triggers

The execution of the trigger is activated by the triggering event and executed automatically by the database server
Multiple triggers may be defined on a data table
When multiple triggers on the same table are activated, the following order of execution is followed:
(1) Executing the Before trigger on the table;
(2) The SQL statement that activates the trigger;
(3) Executes the AFTER trigger on the table.

［例20］执行修改某个教师工资的SQL语句，激活上述定义的触发器。     UPDATE Teacher SET Sal=800 WHERE Ename=‘陈平‘;    执行顺序是：执行触发器Insert_Or_Update_Sal执行SQL语句“UPDATE Teacher SET Sal=800 WHERE Ename=‘陈平‘;

Delete Trigger

SQL syntax for deleting triggers:
DROP TRIGGER < trigger name > on < table name >;
The trigger must be a trigger that has already been created and can only be deleted by a user with the appropriate permissions.

［例21］  删除教师表Teacher上的触发器Insert_Sal     DROP TRIGGER Insert_Sal ON Teacher;

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

mechanism of RDBMS integrity implementation
Integrity constraint definition mechanism
Integrity Check Mechanism
The action that the RDBMS should take when violating the integrity constraint condition

Database-triggers and integrity limits in a domain