首页 - 技术栈

房产交易网站建设策划案深圳宝安建网站

作者: 五速梦信息网
时间: 2026年03月21日 11:17

当前位置：首页 > news >正文

房产交易网站建设策划案,深圳宝安建网站,如何创建公众号微信,wordpress转换app第十四周实验【例】功能要求#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

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

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