實(shí)驗(yàn)12　 觸發(fā)器.doc

實(shí)驗(yàn)12 觸發(fā)器姓名：學(xué)號：專業(yè)：班級：同組人：實(shí)驗(yàn)日期：【實(shí)驗(yàn)?zāi)康呐c要求】n 理解什么是觸發(fā)器n 掌握觸發(fā)器的設(shè)計(jì)與使用【實(shí)驗(yàn)內(nèi)容與步驟】相關(guān)說明：本實(shí)驗(yàn)中前面實(shí)驗(yàn)創(chuàng)建的觸發(fā)器可能對后面的實(shí)驗(yàn)產(chǎn)生干擾，若出現(xiàn)這種情況，請把前面觸發(fā)器刪除，再完成后面實(shí)驗(yàn)。建議每完成一個(gè)實(shí)驗(yàn)題后，即將該題相關(guān)的觸發(fā)器刪除，以免對后面實(shí)驗(yàn)產(chǎn)生干擾。12.0 觸發(fā)器創(chuàng)建與刪除語法1觸發(fā)器的創(chuàng)建觸發(fā)器是一種在發(fā)生數(shù)據(jù)庫事件時(shí)自動運(yùn)行的PL/SQL語法如下：CREATE OR REPLACE TIGGER觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件ON表名FOR EACH ROWBEGINpl/sql語句END2觸發(fā)器的刪除DROP TRIGGER trigger_name12.1 觸發(fā)器基礎(chǔ)以下程序展示的是觸發(fā)器的基本使用方法，請閱讀并理解以下程序代碼，給出運(yùn)行結(jié)果。1.創(chuàng)建測試表-drop table employees;create table employees( id number(5), name varchar2(30), salary number(8,2), job_id varchar2(20);2.創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER secure_emp_1 -這里不能有IS BEFORE INSERT ON employees - 這里沒有分號BEGIN IF (TO_CHAR (SYSDATE,DY) IN (星期六,星期天) OR (TO_CHAR(SYSDATE,HH24:MI) NOT BETWEEN 08:00 AND 18:00 ) THEN RAISE_APPLICATION_ERROR(-20500,你只能在工作時(shí)間對表進(jìn)行操作); END IF; END;/3.測試觸發(fā)器（1）-測試語句insert into employees values (1,a,2222,aaaaa);給出測試結(jié)果截圖：（2）將系統(tǒng)時(shí)間修改為周六，再Insert一條記錄，給出并比較兩次運(yùn)行的結(jié)果。給出測試結(jié)果截圖：12.2 使用觸發(fā)器監(jiān)控?cái)?shù)據(jù)更新操作閱讀以下程序，理解程序功能，給出運(yùn)行測試結(jié)果。1.創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER secure_emp_2 BEFORE INSERT OR UPDATE OR DELETE ON employeesBEGIN -如果當(dāng)前時(shí)間是周六或周日 或者時(shí)間不在8:00-18:00之間 IF (TO_CHAR (SYSDATE,DY) IN (星期五,星期天) OR (TO_CHAR(SYSDATE,HH24:MI) NOT BETWEEN 08:00 AND 18:00 ) THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502,你只能在工作時(shí)間刪除員工表的數(shù)據(jù)); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20500,你只能在工作時(shí)間插入員工表的數(shù)據(jù).); ELSIF UPDATING (SALARY) THEN RAISE_APPLICATION_ERROR (-20503,你只能在工作時(shí)間更新員工表的數(shù)據(jù)); ELSE RAISE_APPLICATION_ERROR (-20504,你只能在工作事件操作員工表的數(shù)據(jù).); END IF; END IF;END;/2.運(yùn)行測試分別運(yùn)行以下測試語句，給出運(yùn)行結(jié)果。并理解為什么會有這樣的結(jié)果。insert into employees values (1,a,2222,AD_PRES);insert into employees values (2,b,2222,AD_VP);給出測試結(jié)果截圖：delete from employees;給出測試結(jié)果截圖：update employees set salary=3000;給出測試結(jié)果截圖：12.3 在insert或update中使用:new閱讀并理解以下程序，理解其功能，給出運(yùn)行測試結(jié)果。1.創(chuàng)建觸發(fā)器-在insert或update中使用:new-CREATE OR REPLACE TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN - 插入和修改可以使用:new來訪問新的數(shù)據(jù),修改也可以使用:old來訪問舊的數(shù)據(jù) IF NOT (:NEW.job_id IN (AD_PRES, AD_VP) AND :NEW.salary 15000 THEN RAISE_APPLICATION_ERROR (-20202,員工不能賺到這么多薪水); END IF;END;2.運(yùn)行測試-測試語句insert into employees values (1,a,20000,AD_PRES);insert into employees values (2,b,20000,AD_VPs);update employees set salary=30000 ,job_id=xxxx where name=a;給出運(yùn)行結(jié)果：思考：觀察運(yùn)行結(jié)果，比較insert和update語句執(zhí)行的差別。12.4 在delete中使用:old閱讀并理解以下程序，理解其功能，給出運(yùn)行測試結(jié)果。1.創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER restrict_salary_del BEFORE DELETE ON employees FOR EACH ROW BEGIN - 在delete語句中只能使用:old來訪問舊的數(shù)據(jù) IF (:OLD.job_id IN (AD_PRES, AD_VP) AND (:OLD.salary 15000) THEN RAISE_APPLICATION_ERROR (-20202,該員工不可以刪除); END IF;END;2.運(yùn)行測試-測試語句insert into employees values (2,張三,20000,AD_PRES);delete from employees where name=張三;給出運(yùn)行結(jié)果：12.5 INSTEAD OF類型的觸發(fā)器閱讀并理解以下程序，理解其功能，給出運(yùn)行測試結(jié)果。1.準(zhǔn)備工作：創(chuàng)建觸發(fā)器及相應(yīng)測試用表-INSTEAD OF類型的觸發(fā)器drop table departments;create table departments( dept_id number(5) primary key, dept_name varchar2(20);drop table employees;create table employees( employee_id number(5), employee_name varchar2(20), department_id number(5), constraint empl_dept_fk foreign key (department_id) references departments(dept_id);-創(chuàng)建視圖create or replace view v_empasselect e.employee_id,e.employee_name,e.department_id,d.dept_namefrom employees e,departments dwhere e.department_id = d.dept_id-創(chuàng)建觸發(fā)器create or replace trigger new_view_emp_dept instead of insert on v_emp for each rowbegin if inserting then insert into departments values(:new.department_id,:new.dept_name); insert into employees values(:new.employee_id,:new.employee_name,:new.department_id); end if;end;2.運(yùn)行測試（1）查詢視圖：select * from v_emp;給出運(yùn)行結(jié)果：（2）插入測試數(shù)據(jù)-向視圖中插入數(shù)據(jù)insert into v_emp values (171,張六,1000,銷售部);（3）結(jié)果測試select * from employees;給出運(yùn)行結(jié)果：select * from departments;給出運(yùn)行結(jié)果：12.6 系統(tǒng)級觸發(fā)器閱讀并理解以下程序，理解其功能，給出運(yùn)行測試結(jié)果。1.創(chuàng)建觸發(fā)器-系統(tǒng)觸發(fā)器-創(chuàng)建登陸或者退出數(shù)據(jù)庫日志表drop table logs;create table logs(userid varchar2(20),times date,op varchar2(10) 操作類型);-登陸數(shù)據(jù)庫觸發(fā)器create or replace trigger log_onafter logon on schemabegin insert into logs values(user,sysdate,logon);end;/-退出數(shù)據(jù)庫觸發(fā)器create or replace trigger log_offbefore logoff on schemabegin insert into logs values(user,sysdate,logoff);end;/2.運(yùn)行測試(1)-以不同的用戶身份登錄和退出數(shù)據(jù)庫數(shù)次，以獲得相關(guān)數(shù)據(jù)如：conn sys/testdb as sysdba; Conn soctt/tiger (2)- A設(shè)置日期的顯示格式alter session set nls_date_format=YYYY-MM-DD HH_MI_SS;(3)- 查看記錄select * from logs;給出運(yùn)行結(jié)果：12.7 觸發(fā)器相關(guān)練習(xí)1.實(shí)驗(yàn)練習(xí)：追蹤薪水變動情況請創(chuàng)建一個(gè)名稱為 change_record的觸發(fā)器，實(shí)現(xiàn)當(dāng)對emp表更新員工工資之后，將更新紀(jì)錄保存到表salary_change_record中。具體要求如下：建立日志對薪水的變動情況形成一個(gè)追蹤，也就是說，如果對某個(gè)職員的薪水進(jìn)行變更就應(yīng)該將其相應(yīng)的變更記錄全部記下來，將數(shù)據(jù)記錄到表salary_change_record（empid,old_salary,new_salary,change_date）中，其中old_salary:用來紀(jì)錄員工原來的工資，new_salary：用來紀(jì)錄更新后的工資，change_date：記錄更新的系統(tǒng)時(shí)間。請給出相應(yīng)的代碼：在表中添加或修改幾行數(shù)據(jù)，而后查看表salary_change_record中數(shù)據(jù)。請給出測試結(jié)果：2編寫一個(gè)數(shù)據(jù)庫觸發(fā)器，當(dāng)任何時(shí)候某個(gè)部門從dept中刪除時(shí)，該觸發(fā)器將從emp表中刪除該部門的所有雇員記錄。給出相應(yīng)的代碼：CREATE OR REPLACE TRIGGER del_emp_deptnoBEFORE DELETE ON deptFOR EACH ROWBEGINDELETE FROM emp WHERE deptno = :OLD.deptno;END;給出測試結(jié)果：3. 映射emp表中每個(gè)部門的總?cè)藬?shù)和總工資（1）-創(chuàng)建映射表CREATE TABLE dept_sal AS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_salFROM empGROUP BY deptno;DESC dept_sal;（2）-創(chuàng)建觸發(fā)器，映射emp表中每個(gè)部門的總?cè)藬?shù)和總工資創(chuàng)建一觸發(fā)器，當(dāng)emp表中數(shù)據(jù)改變時(shí)，自動修改dept_sal表中數(shù)據(jù)，使其反映出Emp表中數(shù)據(jù)的最新狀態(tài)。請給出程序源碼：CREATE OR REPLACE TRIGGER emp_info AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE CURSOR cur_emp IS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno; BEGIN DELETE dept_sal; -觸發(fā)時(shí)首先刪除映射表信息 FOR v_emp IN cur_emp LOOP -DBMS_OUTPUT.PUT_LINE(v_emp.deptno | v_emp.total_emp | v_emp.total_sal); -插入數(shù)據(jù) INSERT INTO dept_sal VALUES(v_emp.deptno,v_emp.total_