约束与触发器

[b]一、键值约束[/b]
1.主键：primary key.
2.Unique.
3.外键.
4.索引.

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

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

[b]二、属性或元组上的约束[/b]
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');

[b]三、模式层的约束——断言与触发器[/b]
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

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

[b]1.ORACLE[/b]

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;