创建触发器

   这些过程中既涉及了update的用法，又涉及了多种触发器的创建，因而记录一下，还有在写sql语句的过程中发现nvl方法的一个用法很有用，习惯的使用方式是：nvl(col1,'0'/*或者是别的指定的值*/)，另一个值得使用的方式：nvl(col1,col2),如果要取的col1的值为null，则取第col2的值，这两个列可以是一个表中的，也可以是不同表中的，nvl在计算的时候非常有用；其它的还有decode、case、to_number、to_date、trunc、sum、max(min)、avg、sysdate(dual)、rownum等等
--1、用户表更新时同步到my_person
create or replace trigger MY_TRI_USER
after insert or update or delete on t_user
for each row
declare
row_count number;
begin
  if inserting or updating then
      select count(*) into row_count from my_person where id=:new.user_id;
      if(row_count>0) then  --修改
         update amy_person a            set (name,login_name,password,idcard,birth_date,sex,mail)=
                (select u.name,u.user_name,u.password,e.certi_code,e.birthday,decode(e.gender,'M','1','F','2','未说明'),e.email from t_user u,t_employee e where u.user_id=e.emp_id  and u.user_id = a.id)
            where exists (select 1 from t_user u,t_employee e where u.user_id = a.id and u.user_id=e.emp_id);
       else   --新增
          insert into my_person(id,name,login_name,password,idcard,birth_date,sex,mail,tel)
          select u.user_id,u.user_name,u.password,e.certi_code,e.birthday,decode(e.gender,'M','1','F','2','未说明'),e.email,e.telph
            from t_user u,t_employee e where u.user_id=e.emp_id and rownum=1;
       end if;
  end if;
  if deleting then
      delete from my_person where id=:old.user_id;
  end if;
end;
--2、用户更新电话时同步到my_person

create or replace trigger MY_TRI_USER_FILTER
after update of tel on MY_USER_FILTER
for each row
begin
   update my_person set tel=:new.tel where id = :new.id;
end;

3、创建基于视图的instead of触发器【以下示例是摘抄的】
create or replace trigger tri_emp_view
instead of update
on my_emp_view
for each row
declare
begin
update emp set ename = :new.ename
where empno = :new.empno;

update dept set dname = :new.dname
where deptno = :new.deptno;
end;


触发器死锁
在itpub上看到的一段语句，特记录下来：怎么确定是出现了死锁或者锁等待的？

select '阻塞者(' || sb.sid || ':' || sb.serial# || '-' || sb.username || ')-' ||
       qb.sql_text blockers,
       '等待者(' || sw.sid || ':' || sw.serial# || '-' || sw.username || ')-' ||
       qw.sql_text waiters
  from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql qb, v$sql qw
where lb.sid = sb.sid
   and lw.sid = sw.sid
   and sb.prev_sql_addr = qb.address
   and sw.sql_address = qw.address
   and lb.id1 = lw.id1
   and sb.lockwait is null
   and sw.lockwait is not null
   and lb.block = 1;