oracle——笔记——触发器简介

浅浅的花香味﹌ 2023-10-02 08:13 79阅读 0赞

create table emp2 ( empno number(4),
                        ename varchar2(10),
                        job varchar2(9),
                        mgr number(4),
                        hiredate date,
                        sal number(7,2),
                        comm number(7,2),
                        deptno number(2) 
                     );
    -----------------------------------------------------------------------------------------------------------
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);
    
    insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);
    
    =============================================================================================================================
    
    create table dept2 ( deptno number(2),
                         dname varchar2(14),
                         loc varchar2(13)
                       );
    
    ----------------------------------------------------
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (10, 'ACCOUNTING', 'NEW YORK');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (20, 'RESEARCH', 'DALLAS');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (30, 'SALES', 'CHICAGO');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (40, 'OPERATIONS', 'BOSTON');
    
    ============================================================
    
    create table salgrade2 ( grade number,
                             losal number,
                             hisal number
                           );
    
    --------------------------------------------------
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (1, 700, 1200);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (2, 1201, 1400);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (3, 1401, 2000);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (4, 2001, 3000);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (5, 3001, 9999);
    
    
    ========================================================
    
    select * from emp2;
    
    select * from dept2;
    
    select * from salgrade2;
    
    =====================================
    select * from emp2;        --employee2 员工表
    select * from dept2;       --department2 部门表
    select * from salgrade2;   -- salary grade2 工资等级表
    
    -----------------------------------------------------
    emp2
    
    empno    员工编号
    ename    员工姓名
    job      工作/工种
    mgr      manager上级编号
    hiredate 入职日期
    sal      salary 工资
    comm     奖金/津贴
    deptno   部门编号
    ---------------------
    
    dept2
    
    deptno  部门号
    dname   部门名称
    loc     所在地
    ---------------------------
    salgrade2
    
    grade                   等级
    losal lowest salary     最低工资
    hisal high salary       最高工资
    
    =============================================
    
     
    
    触发器：
    
      销售表  销售数量  +100
      库存表  库存  -100
    
    create table spb(            --商品表
    spbm         integer,         --商品编码
    spmc         varchar2(30),    --商品名称
    shangbi         varchar2(20),    --商标
    chandi          varchar2(12),    --产地
    guige         varchar2(20),    --规格
    danwei         varchar2(4),    --单位
    danjia         number(10,2),    --单价
    kucun         number(10,2)    --库存数量
    );
    
    
    
    
    create table stock(        --采购表
    djh     integer,        --单据号
    spbm     integer,        --商品编码
    cgy     varchar2(8),    --采购员
    cgd     varchar2(30),    --采购地
    cgsl     number(10,2),    --采购数量
    cgpc     varchar2(16),    --采购批次    
    cgrq     date);        --采购日期
    
    
    
    
    创建第一个触发器：
    
    一旦采购表中被插入采购商品的记录，就更新商品表的数据，使相应的商品的库存增加：
    
    create or replace trigger stock_ins
    after insert on stock
    for each row
    begin
    update spb set kucun=kucun + :NEW.cgsl where spbm=:NEW.spbm;
    end;