泉州企业建站系统,中国网站建设集团,海南网站seo,沈阳市城乡建设局网站第十四周实验 【例】功能要求#xff1a;增加一新表XS_1#xff0c;表结构和表XS相同#xff0c;用来存放从XS表中删除的记录。 分析:
1、创建表 xs_1
SQL create table xs_1 as select * from xs; Table created SQL truncate table xs_1; Table truncated题目增加一新表XS_1表结构和表XS相同用来存放从XS表中删除的记录。 分析:
1、创建表 xs_1
SQL create table xs_1 as select * from xs; Table created SQL truncate table xs_1; Table truncated题目创建一个触发器当XS表中记录被删除时请备份下删除的记录方式写到新建表XS_1中,以备查看。 create or replace trigger del_xs before delete on xs for each row begin insert into xs_1(xh,xm,zym,xb,cssj,zxf) values (:old.xh,:old.xm, :old.zym, :old.xb, :old.cssj,:old.zxf); end del_xs; 代码
SQL select *from xs_1; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- -------------------- SQL select *from xs; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
001 张琼 计算机 45 三好学生
121112 王小二 计算机 男 1986/1/30 36 SQL delete from xs where xh001; 1 row deleted SQL select *from xs_1; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
001 张琼 计算机 45 SQL select *from xs; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
121112 王小二 计算机 男 1986/1/30 36 SQL 综上所述备份成功 触发器示例2
功能需求监控用户对XS表的操作要求当XS表执行插入、更新和删除3种操作后在sql_info表中给出相应提示和执行时间。
Create table sql_info(info varchar(10),time date); 思考是否可以放到一个触发器中如可以则需要判断
到底是哪种操作插入还是更新还是删除 create or replace trigger t2 after delete or insert or update on xs for each row
declare v_info sql_info.info%type;
begin if inserting then v_info:插入; elsif updating then v_info:更新; else v_info:删除; end if; insert into SQL_INFo VALUES(v_info,sysdate); end t2;
SQL create table sql_info(info varchar(10),time date); Table created SQL select * from sql_info; INFO TIME
---------- -----------、 SQL insert into xs(xh) values(21212); 1 row inserted SQL select * from sql_info; INFO TIME
---------- -----------
插入 2023/12/8 1 SQL SQL select * from xs; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
121112 王小二 计算机 男 1986/1/30 36
21212 SQL delete from xs where xh21212; 1 row deleted SQL select * from xs_1; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
001 张琼 计算机 45
21212 SQL select * from sql_info; INFO TIME
---------- -----------
插入 2023/12/8 1
删除 2023/12/8 1 SQL 1.
SQL create table emp_1 as select * from scott.emp; Table created SQL truncate table emp_1; Table truncated SQL create or replace trigger del_scott before delete on scott.emp for each row
begin insert into emp_1 (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end del_emp; 测试;
SQL delete from scott.emp where empno 7499; 1 row deleted SQL select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 820.00 20 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1120.00 20 7900 JAMES CLERK 7698 1981/12/3 970.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 13 rows selected SQL select * from emp_1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 SQL 2.
create or replace trigger t2 after delete or insert or update on xs for each row
declare v_info sql_info.info%type;
begin if inserting then v_info:插入; elsif updating then v_info:更新; else v_info:删除; end if; insert into SQL_INFo VALUES(v_info,sysdate); end t2;
SQL create table sql_info(info varchar(10),time date); Table created SQL select * from sql_info; INFO TIME
---------- ----------- SQL insert into xs(xh) values(21212); 1 row inserted SQL select * from sql_info; INFO TIME
---------- -----------
插入 2023/12/8 1 SQL select * from xs; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
121112 王小二 计算机 男 1986/1/30 36
21212 SQL delete from xs where xh21212; 1 row deleted SQL select * from xs_1; XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
001 张琼 计算机 45
21212 SQL select * from sql_info; INFO TIME
---------- -----------
插入 2023/12/8 1
删除 2023/12/8 1 SQL update scott.emp set enameCHenwang where empno7900; 1 row updated SQL select * from sql_info; INFO TIME
---------- -----------
插入 2023/12/8 1
删除 2023/12/8 1 SQL
3.
CREATE OR REPLACE TRIGGER op_emp
BEFORE INSERT OR UPDATE OR DELETE ON scott.emp
FOR EACH ROW
BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE(:new.empno|| ||:new.ename); ELSIF UPDATING THEN DBMS_OUTPUT.PUT_LINE(:old.sal|| ||:new.sal); ELSE DBMS_OUTPUT.PUT_LINE(:old.empno|| || :old.ename); END IF;
END op_emp; SQL set serveroutput on;
SQL insert into scott.emp(empno) values(7369); 7369 1 row inserted SQL update scott.emp set enameLAOLI where empno7369; 1 row updated SQL delete from scott.emp where empno 7369; 7369 LAOLI 1 row deleted 4.
CREATE OR REPLACE TRIGGER t4 AFTER INSERT OR UPDATE OR DELETE ON scott.emp
declare v_1 number; v_2 scott.emp.sal%type;
begin if inserting then select count(*) into v_1 from scott.emp; DBMS_OUTPUT.PUT_LINE(添加记录后总人数为||v_1); elsif updating then select avg(sal) into v_2 from scott.emp; DBMS_OUTPUT.PUT_LINE(更新记录后平均工资为|| ||v_2); else for v_s in (select deptno,count(*) num from scott.emp group by deptno) loop DBMS_OUTPUT.PUT_LINE(删除记录后各个部门的部门号和人数为 ||v_s.deptno|| ||v_s.num); end loop; end if;
end t4; SQL insert into scott.emp(empno) values(7369); 7369
添加记录后总人数为13 1 row inserted SQL update scott.emp set enameLAOLI where empno7369; 更新记录后平均工资为 2218.75 1 row updated SQL delete from scott.emp where empno 7369; 7369 LAOLI
删除记录后各个部门的部门号和人数为30 5
删除记录后各个部门的部门号和人数为20 4
删除记录后各个部门的部门号和人数为10 3 1 row deleted
