科普数据库（三）

create trigger 触发器
before|afte
insert|update|delete
on 表名
[for each row]--行级触发
declar
 --声明部
begi
 --业务逻辑
end

create [or replace] trigger 触发器
before|afte
insert|update|delete
on 表名
[for each row]--行级触发
declar
 --声明部
begi
 --业务逻辑
end

drop trigger 触发器名

1、INSERT 型触
--新员工入职之后，输出一句话: 欢迎加入
create or replace trigger tri_emp_inser
afte
inser
on em
declar
beg
  dbms_output.put_line('欢迎加入我们')
end
--插入数据就可以自动触发触
insert into emp(empno, ename) values(9527, '马哈哈')

--判断员工涨工资后的工资一定要大于涨工资前的
create or replace trigger tri_emp_update_sa
befor
updat
on em
for each ro
declar
beg
  if :old.sal > :new.sal the
    raise_application_error(-20002,'旧的工资不能大于新的工资')
  end if
end

update emp set sal = sal + 10
select * from emp

update emp set sal = sal - 100
select * from emp

--老员工离职之后，输出一句话: 有员工离
create or replace trigger tri_emp_delet
afte
delet
on em
declar
beg
  dbms_output.put_line('有员工离职了')
end
--删除数据就可以自动触发触
delete from emp where empno = 9527

drop trigger tri_emp_inser
drop trigger tri_emp_update_sal
drop trigger tri_emp_delete

上
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zi
注意：unzip可能会提示没有这个命令，那就需要我们安装
yum -y install zi
yum -y install unzi

[root@caochenlei ~]# mkdir -p /usr/local/oracl
[root@caochenlei ~]# mv database/ /usr/local/oracl

[root@caochenlei ~]# hostna
caochenle

[root@caochenlei ~]# ifconf

[root@caochenlei ~]# vi /etc/hos

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomai
::1         localhost localhost.localdomain localhost6 localhost6.localdomain
192.168.112.129 caochenl

[root@caochenlei ~]# vi /etc/selinux/confi
设置SELINUX=disable
临时关闭一下selinu
[root@caochenlei ~]# setenforce

[root@caochenlei ~]# service iptables sto
[root@caochenlei ~]# chkconfig iptables of

[root@caochenlei ~]# yum install -y binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel make sysstat unixODBC-devel pdksh java-1.8.0-openjdk

[root@caochenlei ~]# groupadd oinstal
[root@caochenlei ~]# groupadd db
[root@caochenlei ~]# useradd -g oinstall -G dba oracl
[root@caochenlei ~]# passwd oracl

[root@caochenlei ~]# vi /etc/sysctl.con

fs.aio-max-nr = 10485
fs.file-max = 681574
kernel.shmall = 209715
kernel.shmmax = 53687091
kernel.shmmni = 409
kernel.sem = 250 32000 100 12
net.ipv4.ip_local_port_range = 9000 6550
net.core.rmem_default = 26214
net.core.rmem_max = 419430
net.core.wmem_default = 26214
net.core.wmem_max = 104857

[root@caochenlei ~]# sysctl

[root@caochenlei ~]# vi /etc/security/limits.con

oracle            soft    nproc           40
oracle            hard    nproc           6553
oracle            soft    nofile          409
oracle            hard    nofile          6553

[root@caochenlei ~]# rebo

[root@caochenlei ~]# mkdir -p /data/oracle/product/11.2.
[root@caochenlei ~]# mkdir -p /data/oracle/oradat
[root@caochenlei ~]# mkdir -p /data/oracle/inventor
[root@caochenlei ~]# chown -R oracle:oinstall /data/oracl
[root@caochenlei ~]# chmod -R 775 /data/oracl

[root@caochenlei ~]# su - orac
[oracle@caochenlei ~]$ vi .bash_profil

ORACLE_BASE=/data/orac
ORACLE_HOME=$ORACLE_BASE/product/11.2.
ORACLE_SID=orc
PATH=$PATH:$ORACLE_HOME/bi
export ORACLE_BASE ORACLE_HOME ORACLE_SID PAT

[oracle@caochenlei ~]$ source .bash_profi

[oracle@caochenlei response]$ cd /usr/local/oracle/databas
[oracle@caochenlei database]$ LANG=en_US ./runInstalle

[root@caochenlei ~]# sh /data/oracle/inventory/orainstRoot.
Changing permissions of /data/oracle/inventory
Adding read,write permissions for group
Removing read,write,execute permissions for world
Changing groupname of /data/oracle/inventory to oinstal
The execution of the script is complete
[root@caochenlei ~]# sh /data/oracle/product/11.2.0/dbhome_1/root.s
Running Oracle 11g root.sh script..
The following environment variables are set a
    ORACLE_OWNER= oracl
    ORACLE_HOME=  /data/oracle/product/11.2.0/dbhome_
Enter the full pathname of the local bin directory: [/usr/local/bin]
   Copying dbhome to /usr/local/bin ..
   Copying oraenv to /usr/local/bin ..
   Copying coraenv to /usr/local/bin ..
Creating /etc/oratab file
Entries will be added to the /etc/oratab file as needed b
Database Configuration Assistant when a database is create
Finished running generic part of root.sh script
Now product-specific root actions will be performed
Finished product-specific root actions

ORCL
  (DESCRIPTION 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.112.129)(PORT = 1521)
    (CONNECT_DATA 
      (SERVER = DEDICATED
      (SERVICE_NAME = orcl