Oracle触发器简单实例

select * from login;
select * from czf6;
insert into czf6 values(29,'sde');
----1、定义一个触发器，完成insert后，提示“insert语句插入成功”
drop trigger logininsertafter;


create or replace trigger logininsertafter
after insert on czf6
begin
   dbms_output.put_line('insert语句插入成功');
end;


select * from login;
insert into login values(5,'s36','s36');


--2、定义一个触发器，在insert之前，提示“即将进行insert语句操作”
create or replace trigger logininsertbefore
before  insert on czf6
begin
   dbms_output.put_line('即将进行insert语句操作');
end;
--3、定义一个触发器，在插入或修改时，先检查密码的长度，
--如果小于6,则密码的值为'123456’


create or replace trigger checkpwdlength
before  insert or update  on login
  for each row
  when(length(new.password)<6)
begin
    :new.password:='123456';
end;


insert into login values(13,'sdw','we2232323');
update login set password='123' where id=1;
select * from login;
--4、定义一个触发器，实现Oracle
--中序列和触发器的使用
drop sequence l_seq;
create sequence l_seq;
delete from login;
select l_seq.nextval from dual;


create or replace trigger seq_trigger
before insert on login
 for each row
 begin
   --:new.id:=l_seq.nextval;虚拟表
   select l_seq.nextval  into :new.login_id from dual ;
 end;
 
 insert into login values(l_seq.nextval,'2sd','we');


    
  insert into login(login_name,password) values('2sd','we');


      
select * from login;


 drop trigger checkpwdlength;
 drop trigger seq_trigger;
 --5、定义一个触发器，实现自增功能
 --方法一
 
 drop trigger seq_trigger;
 
 create or replace trigger seq_trigger
 before insert on stu2
 for each row
  declare
   num number:=0;
   begin
      select count(*) into num from stu2;
        if num=0 then
           num:=1;
        else
        select max(id)  into num from stu2 ;
           num:=num+1;
        end if;
        select num into :new.id from dual;   
  end;
  
  create table stu2(id number(10),name varchar2(40));
 
  select *　from stu2;
  delete from login;
  
  --方法二
  create  or replace trigger seq_trigger
  before insert on login
  for each row
  declare 
  cursor c is select id from login;
  num number;
  begin
    open c;
    fetch c into num;
    if c%rowcount=0 then 
       num:=1;
       else
       select (select max(id) from login)+1 into num from dual;
    end if;
       :new.id:=num;
       close c;
  end;
  
  delete  from login;
  insert into login(name,password) values('sds25','asas4');
select * from login;


--6、编写一个触发器，判断是哪种DML操作
--ddl:drop create alter
--dcl:grant revoke
--dml:insert update delete 
--dql:select 
create  or replace trigger dml
before insert or update or delete 
on stu2
begin
if deleting then 
dbms_output.put_line('delete....');
elsif updating then
dbms_output.put_line('update....');
elsif inserting then 
dbms_output.put_line('inserting....');
end if;
end;
 insert into stu2(name) values('12');
 delete from stu2;
 update stu2 set name='s334' where  id=12;
 
--7、DML操作的用户进行安全检查，看是否具有合适的特权
begin
 dbms_output.put_line(user);
end;
drop trigger CheckAuth;


create or replace trigger CheckAuth
before insert or update or delete on s33.s33
begin
  if user not in('CZF','SYSTEM') then 
   Raise_application_error('-20001','you have not access to this table');
  end if;
end;




drop user s33 cascade;
--创建一个oracle用户
create user s33
identified by s33;
--给用户分配权限
grant connect to s33;


grant resource to s33;


revoke connect from s33;


----8、对表的操作、时间、人物进行日志记录。
drop table login_log;
create table login_log(
 v_id number(4) primary key,
Operation varchar2(50) not null,
v_when date not null,
people varchar2(40) not null
);


drop sequence log_seq;
create sequence log_seq;
create or replace trigger log_trigger
after update or insert or delete on login
declare 
  operation varchar2(40);
begin
  if deleting then 
  operation:='delete';
elsif updating then
operation:='update';
elsif inserting then 
operation:='insert';
end if;
  insert into login_log values(log_seq.nextval,operation,sysdate,user);
end;
  insert into login values(12,'sds25','asas4');
select * from login_log;
--9、打开和关闭数据库的触发器
create table database_ss(
  operation varchar2(40) not null,
  v_when date not null
);


create or replace trigger db_trigger_start
 after  startup on database
begin
 insert into database_ss values('startup',sysdate);
 end;
 
 create or replace trigger db_trigger_shutdown
  before shutdown on database
begin
 insert into database_ss values('shutdown',sysdate);
 end;
 
 select * from database_ss
 --10 编写触发器，用来记载登录用户名称、时间和ip地址
 drop table jax_log_table;
 create table jax_log_table(
username varchar2(20), 
log_time date, 
onoff varchar(6),
address varchar2(30)
);
create or replace trigger login_on_off
after logon on database 
begin
insert into jax_log_table values(ora_login_user,sysdate,'logon',ora_client_ip_address);
end;


create or replace trigger offlogin
before logoff on database 
begin
insert into jax_log_table values(ora_login_user,sysdate,'logoff',ora_client_ip_address);
end;




select * from jax_log_table;
select * from  student;
--11、编写触发器，记载DDL事件。
drop table jax_event_ddl_table;


create table jax_event_ddl_table(
event varchar2(20),
username varchar2(10),
owner varchar2(10),
objname varchar2(20),
objtype varchar2(10),
time date);


create or replace trigger ddl_log
after ddl on database
begin
insert into jax_event_ddl_table values(ora_sysevent,ora_login_user,
ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
 
create sequence f_seq;
drop sequence f_seq;


select * from jax_event_ddl_table
drop sequence f_seq;
drop table czf8;
create table czf8(id number(10));


create view v_czf8 as select * from czf8;


create or replace view s_view as select * from student;
alter table student rename column stduentno to studentno;
--12、创建一个instead of 触发器
create table department(deptno number(20),dname varchar2(40));


create table employee(empno number(20),ename varchar2(40),
deptno number(20));
  create or replace view d_e_view as
select d.deptno,e.empno,e.ename,d.dname
from employee e,department d
where e.deptno=d.deptno
select * from department;
select * from employee;


select *from d_e_view;
insert into d_e_view values(1,1001,'梨花','广告部');


create  or replace trigger d_e_trigger
instead of insert on d_e_view
declare
v_no number;
begin
   select count(*) into v_no from department where deptno=:new.deptno;
   if v_no=0 then
   insert into  department values(:new.deptno,:new.dname);
   end if;
   select count(*) into v_no from employee where empno=:new.empno;
   if v_no=0 then
   insert into  employee values(:new.empno,:new.ename,:new.deptno);
   end if;
end;
--
create or replace view login_view
as select * from student  where studentname like '李%' 
with check option;
--只能操作满足该视图约束条件的数据
select * from login_view;
insert into login_view values('s301234519','sdwe23','王李虎','男',
3,'23345454','beijing','1-1月-12','fg@163.com');


delete from login_view where studentno='s301234511' 


--13、当删除、添加、修改一个表字段时，另一个表的字段做相应的操作。
create table reg(
id number primary key,
name varchar2(40) not null,
pwd  varchar2(20) not null,
hobby varchar2(50)
);
create table login2(
id number,
name varchar2(40) ,
pwd  varchar2(20) 
);
create or replace trigger reg_login2_trigger
after insert or update or delete on reg
for each row
begin
    if inserting then 
    insert into login2 values(:new.id,:new.name,:new.pwd);
    end if;
     if updating then 
    update login2 set name=:new.name,pwd=:new.pwd where id=:old.id;
    end if;
     if deleting then 
     --删除的时候用:old.id，不用：new.id
    delete from login2 where id=:old.id;
    end if;
end;
insert into reg values(1,'czf','sdfwe','fish');
update reg set pwd='1234',name='czf2' where id=1;
select * from login2;
delete from reg where id=1;
delete from login2 ;
select * from reg;