创建触发器

这些过程中既涉及了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 [b][color=red]a[/color][/b] 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 [b][color=red]u.user_id = a.id[/color][/b])
where exists (select 1 from t_user u,t_employee e where [b][color=red]u.user_id = a.id[/color][/b] 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 [b][color=red]update of tel[/color][/b] 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;