约束与触发器

一、键值约束
    1.主键：primary key.
    2.Unique.
    3.外键.
    4.索引.
   
    键值约束主要有以上4种，其中Unique，外键，索引都可以在关系中存在多个，但primary key只能唯一存在。
    另外，在处理外键约束的插入、删除、更新等操作时DBMS主要提供了三种方法：
    1.引用关系的删除和修改缺省原则：当仅仅当关系中产生一个或多个悬挂元组时，操作被阻止。
    2.级联原则：删除和修改所有新产生的悬挂元组。
    3.置空原则：把每个悬挂元组中的外键值设为Null。

    注：悬挂元组是指外键值在引用关系中不出现的元组。

二、属性或元组上的约束
    1.非空约束：NOT NULL
    2.基于属性的Check约束：
   

parentnum int check (parentnum >= 300000)

    3.基于元组的check约束：
   

    create table person(
       name char(10) primary key,
       age int,
       check(name is not null and age < 10)
    )

   
     操作约束的相关SQL：
     1.使约束在立即执行与延迟执行状态之间转换
    

     --设为立即执行
     setset constraints all immediate
     set constraint
     
     --设为延迟执行
     setset constraints all deferred
     set constraint
     

     2.增、删约束
    

     alert table person drop constraint const_fk;
     alert table person add constraint check(name <> 'xiao');
     

三、模式层的约束——断言与触发器
     1.断言：断言是SQL表达式，并且总是为真。
     创建断言：
    

     create assertion assertion_name check (<condtion>)
     example:
     create assertion rich check(
         not exists(
            select * 
            from studio, movieExec
            where pres = cert and netWorth < 100000
         )
     )

     删除断言：
    

drop assertion assertion_name

    
     2.触发器：事件——条件——动作规则，触发器可以在DBMS监测到指定动作执行之前或之后执行特定的操作。触发器在捕捉动作规则时有三种标准，Before和After是所有DBMS都支持的，而部分厂商同时也提供了对Instead of的支持。另外，触发器在处理影响范围时也包括行级触发(FOR EACH ROW)和语句级触发(FOR EACH STATEMENT)，OLD ROW AS和NEW ROW AS 提供行级操作的新旧元组，OLD TABLE AS和NEW TABLE AS 则指代语句级操作的新旧表。WHEN语句指定的则是触发器的执行操作条件。操作由BEGIN和END语句块指定。以下是几个触发器的例子：

    

     --行级触发器
     create trigger trigger1
     after update of networth on MovieExec
     referencing 
          old row as oldtuple,
          new row as newtuple
     for each row
     when (oldtuple.networth > newtuple.networth) 
     begin
          update MovieExec
          set networth = oldtuple.networth
          where cert = newtuple.cert
     end
     
     --语句级触发器
     create trigger trigger2
     after update of networth on MovieExec
     referencing 
          old row as oldtable,
          new row as newtable
     for each statement
     when (5000 > (select avg(networth) from MoiveExec)
     begin
         delete from MovieExec
         where (name, address, cert, networth) in newtable;
         insert into MovieExec
         (select * from oldtable);
     end

     --替换触发器
     create trigger trigger3
     instead of insert on paramountMoive
     referencing new row as newrow
     for each row
     begin
          insert into moive(title, year, studioname)
          values(newrow.title, newrow.year, 'paramount')
     end
     

由于各种数据库方言而导致触发器在实际操作中略有不同，下附实例

1.ORACLE

create or replace trigger item_trigger
after insert or delete or update of other1 on t_inspect_item
for each row
begin
  if updating then
    begin
      delete from t_inspect_work_item
      where inspectitemid = :old.other1;
      insert into t_inspect_work_item(id, inspectitemid, serialnumber,serialcode, name, point)
      select a.id || :new.id as id,
             a.other1 as insepctitemid,
             a.other2 as serialnumber,
             a.serialcode as serialcode,
             a.name as name,
             a.other3 as point
      from t_inspect_item_type a
      where a.other1 = :new.other1;
    end;
   end if;

  if deleting then
    begin
      delete from t_inspect_work_item
      where inspectitemid = :old.other1 and id like '%'||:old.id;
    end;
  end if;
  
  if inserting then
    begin
      insert into t_inspect_work_item(id, inspectitemid, serialnumber,serialcode, name, point)
      select a.id || :new.id as id,
             a.other1 as insepctitemid,
             a.other2 as serialnumber,
             a.serialcode as serialcode,
             a.name as name,
             a.other3 as point
      from t_inspect_item_type a
      where a.other1 = :new.other1;
    end;
  end if;
end;