oracle触发器

select * from scott.emp
-----------------------------语句级触发器---------------------------
create or replace trigger addinfo_tri
before insert or update or delete on scott.emp
begin
if to_char(sysdate,'dy','nls_date_language=american') in ('sat','sun') then
raise_application_error(-20001,'不能在周末修改信息');
end if;
end;

update scott.emp set sal=5000 where empno=7369;

-----------------------------after级触发器   审计DML操作---------------------------
create table audit_table(
name varchar2(20),
ins int,
upd int,
del int,
starttime date,
endtime date
)tablespace PraMing;


select * from  audit_table;

create or replace trigger alterinfo_tri
after insert or update or delete on scott.emp
declare
v_temp int;
begin
select count(*) into v_temp from audit_table where name='zhang';
if v_temp=0 then
insert into audit_table values('zhang',0,0,0,sysdate,null);
end if;
case
when inserting then
update audit_table set ins=ins+1,endtime=sysdate where name='zhang';
when updating then
update audit_table set upd=upd+1,endtime=sysdate where name='zhang';
when deleting then
update audit_table set del=del+1,endtime=sysdate where name='zhang';
end case;
end;


select * from  audit_table;
insert into audit_table values('lisi',0,0,0,sysdate,null);

update scott.emp set sal=999 where ename='SMITH';


begin
dbms_output.put_line(dbms_utility.get_time());
end;
-----------------------------行级触发器---------------------------
create or replace trigger altersal_tri
before update on scott.emp
for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20009,'不能低于原有工资');
end if;
end;

update scott.emp set sal=99.9 where ename='SMITH';


-------------------instead of  触发器   专用于视图------------------------------------

select * from scott.emp
select * from scott.dept
select e.empno,e.ename,e.sal,d.dname from scott.emp e inner join scott.dept d on d.deptno=e.deptno;

create or replace view allinfo
as
select e.empno,e.ename,e.sal,d.dname from scott.emp e inner join scott.dept d on d.deptno=e.deptno;

select * from allinfo

create or replace trigger allinfoview_tri
instead of update on allinfo
begin
raise_application_error(-20008,'修改信息');
end;