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;
