触发器

语句级触发器
create or replace trigger check_time
before
update or insert or delete
on aspnet_users
begin
if(to_char(sysdate,'DAY）in ('星期六','星期日'))
or to_char(sysdate,'HH24')<'08'
or to_char(sysdate,'HH24’)>='17' then
raise_application_error('-20500,‘非法时间！');
end if;
end;


行级触发器
create or replace trigger casade_update_departid
after
update of deptmentId
on department
for each row
begin
update deptment set deptment.parentDepartID=;NEW.departmentId
where department.parentDepartID=:OLD.DEPARTMENTiD;
UPDATE departmentusers set departmentusers.departmentID=:new.departmentiD
WHERE DepartmentUsers.departmentid=:old.departmentID;
END;




替代触发器(替代触发器只能用于视图上)
create or replace tirgger change_name
instead of
insert 
on v_aspnet_usernaem
decalre
v_userno varchar2(20);
begin
select trim(to_char(max(userno)+1,'0009') into v_userno from
aspnet_users;
insert into aspnet_users(userno,username)values(v_usrno,:new.username);




数据库事件触发器
1.create or replace tirgger int_logon
after
startup
on database
begin
delete from userlog;
end;






create or replace trigger database_logon
after
logon
on database
begin
inser into userlog values(sys.login_user,sysdate);
end;




创建专门记录wms账户的登录时间
create or replace trigger wms_logon
after
logon on wms.schema
begin
inser into userlog values(syslogin_user,sysdate);
end;


DDL触发器
create or replace trigger nodrop_user
defore
drop on schema
begin
if upper(sys.dictionary_obj_name)='aspnet_user'then
raise_application_error(-2005,'错误信息：不能删除ASPNET_USES表’）;
end if;
end;