PL/SQLTriggerPL/SQLTriggerPL/SQLTriggerPL/SQLTrigger资源-CSDN文库

共1个文件

sql：1个

需积分: 5 58 浏览量 2023-08-02 00:36:14 上传评论收藏 3KB ZIP 举报

PL/SQL Trigger是Oracle数据库中的一个关键特性，用于在特定的数据库事件发生时自动执行一段预定义的PL/SQL代码。这些事件可能包括对表的INSERT、UPDATE或DELETE操作，或者其他的数据库活动。触发器是数据库级别的对象，它们在后台工作，对用户透明，为数据库提供了增强的数据完整性控制和业务规则实施。 PL/SQL（Procedural Language/Structured Query Language）是Oracle数据库的扩展SQL语言，结合了SQL的查询能力与过程编程语言的特性，使得开发者能够编写复杂的数据库逻辑。Trigger与PL/SQL的结合使得开发者可以创建复杂的业务规则，实现如审计跟踪、数据验证、并发控制等功能。在PL/SQL Trigger中，有几种常见的类型： 1. **Before Trigger**：这种触发器在DML语句（INSERT, UPDATE, DELETE）执行之前触发，可以用来验证数据、修改操作数据或者执行预处理任务。 2. **After Trigger**：这种触发器在DML语句执行之后触发，通常用于更新索引、记录日志或进行后处理任务。After Trigger不能阻止DML语句的执行，但可以影响返回的结果集。 3. **Instead Of Trigger**：这种触发器用在视图上，代替对基础表的DML操作。Instead Of Trigger定义了当试图在视图上执行DML时应如何操作底层表。触发器的语法结构包括以下几个部分： - **CREATE TRIGGER**：声明开始创建一个新的触发器。 - **trigger_name**：触发器的唯一标识名。 - **[BEFORE | AFTER | INSTEAD OF]**：定义触发器的类型。 - **[FOR EACH ROW]**：如果每个受影响的行都需触发，则包含此选项。 - **WHEN (condition)**：可选条件，只有满足条件时触发器才会执行。 - **BEGIN**：开始PL/SQL代码块。 - **PL/SQL语句**：执行的PL/SQL代码。 - **END;**：结束PL/SQL代码块。在使用触发器时，需要注意以下几点： 1. **性能影响**：频繁触发的触发器可能会影响数据库性能，因此应谨慎设计和使用。 2. **代码隐藏**：由于触发器在后台运行，可能导致代码难以理解和调试。 3. **替代方案**：在某些情况下，触发器可以被存储过程、物化视图、约束或数据库触发的异步作业替代，以提高可维护性和性能。在实际开发中，合理利用PL/SQL Trigger可以有效地强化数据管理，但同时也需要权衡其带来的潜在问题。通过深入理解和熟练运用，我们可以利用这个工具实现更灵活、更安全的数据库应用程序。

资源推荐

资源详情

资源评论

收起资源包目录

sql.zip （1个子文件）

sql

new 2.sql 15KB

CREATE TABLE EMPAUDIT ( EMPID NUMBER, ERRORCODE VARCHAR2(1) CHECK (ERRORCODE IN ('S', 'B', 'C', 'M')), OPERATION VARCHAR2(1) CHECK (OPERATION IN ('I', 'U', 'D')), WORKDEPT VARCHAR2(30), SALARY NUMBER(10, 2), COMM NUMBER(10, 2), BONUS NUMBER(10, 2) ); create table employee (EMPNO char(6), FIRSTNAME varchar(12), MIDINIT char(1), LASTNAME varchar(15), WORKDEPT char(3), PHONENO char(4), HIREDATE date, JOB char(8), EDLEVEL smallint, SEX char(1), BIRTHDATE date, SALARY decimal(9,2), BONUS decimal(9,2), COMM decimal(9,2)); -- INSERT employee DATA INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000010', 'CHRISTINE', 'I', 'HAAS', 'A00', '3978', TO_DATE('19950101', 'YYYYMMDD'), 'PRES ', 18, 'F', TO_DATE('19630824', 'YYYYMMDD'), +0152750.00,+0001000.00,+0004220.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000020', 'MICHAEL', 'L', 'THOMPSON', 'B01', '3476', TO_DATE('20031010', 'YYYYMMDD'), 'MANAGER ', 18, 'M', TO_DATE('19780202', 'YYYYMMDD'), +0094250.00,+0000800.00,+0003300.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000030', 'SALLY', 'A', 'KWAN', 'C01', '4738', TO_DATE('20050405', 'YYYYMMDD'), 'MANAGER ', 20, 'F', TO_DATE('19710511', 'YYYYMMDD'), +0098250.00,+0000800.00,+0003060.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000050', 'JOHN', 'B', 'GEYER', 'E01', '6789', TO_DATE('19790817', 'YYYYMMDD'), 'MANAGER ', 16, 'M', TO_DATE('19550915', 'YYYYMMDD'), +0080175.00,+0000800.00,+0003214.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000060', 'IRVING', 'F', 'STERN', 'D11', '6423', TO_DATE('20030914', 'YYYYMMDD'), 'MANAGER ', 16, 'M', TO_DATE('19750707', 'YYYYMMDD'), +0072250.00,+0000500.00,+0002580.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000070', 'EVA', 'D', 'PULASKI', 'D21', '7831', TO_DATE('20050930', 'YYYYMMDD'), 'MANAGER ', 16, 'F', TO_DATE('20030526', 'YYYYMMDD'), +0096170.00,+0000700.00,+0002893.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000090', 'EILEEN', 'W', 'HENDERSON', 'E11', '5498', TO_DATE('20000815', 'YYYYMMDD'), 'MANAGER ', 16, 'F', TO_DATE('19710515', 'YYYYMMDD'), +0089750.00,+0000600.00,+0002380.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000100', 'THEODORE', 'Q', 'SPENSER', 'E21', '0972', TO_DATE('20000619', 'YYYYMMDD'), 'MANAGER ', 14, 'M', TO_DATE('19801218', 'YYYYMMDD'),+0086150.00,+0000500.00,+0002092.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000110', 'VINCENZO', 'G', 'LUCCHESSI', 'A00', '3490', TO_DATE('19880516', 'YYYYMMDD'), 'SALESREP', 19, 'M', TO_DATE('19591105', 'YYYYMMDD'), +0066500.00,+0000900.00,+0003720.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000120', 'SEAN', ' ', 'O''CONNELL', 'A00', '2167', TO_DATE('19931205', 'YYYYMMDD'), 'CLERK ', 14, 'M', TO_DATE('19721018', 'YYYYMMDD'), +0049250.00,+0000600.00,+0002340.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000130', 'DELORES', 'M', 'QUINTANA', 'C01', '4578', TO_DATE('20010728', 'YYYYMMDD'), 'ANALYST ', 16, 'F', TO_DATE('19550915', 'YYYYMMDD'), +0073800.00,+0000500.00,+0001904.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000140', 'HEATHER', 'A', 'NICHOLLS', 'C01', '1793', TO_DATE('20061215', 'YYYYMMDD'), 'ANALYST ', 18, 'F', TO_DATE('19760119', 'YYYYMMDD'), +0068420.00,+0000600.00,+0002274.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000150', 'BRUCE', ' ', 'ADAMSON', 'D11', '4510', TO_DATE('20020212', 'YYYYMMDD'), 'DESIGNER', 16, 'M', TO_DATE('19770517', 'YYYYMMDD'), +0055280.00,+0000500.00,+0002022.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000160', 'ELIZABETH', 'R', 'PIANKA', 'D11', '3782', TO_DATE('20061011', 'YYYYMMDD'), 'DESIGNER', 17, 'F', TO_DATE('19800412', 'YYYYMMDD'), +0062250.00,+0000400.00,+0001780.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890', TO_DATE('19990915', 'YYYYMMDD'), 'DESIGNER', 16, 'M', TO_DATE('19810105', 'YYYYMMDD'), +0044680.00,+0000500.00,+0001974.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000180', 'MARILYN', 'S', 'SCOUTTEN', 'D11', '1682', TO_DATE('20030707', 'YYYYMMDD'), 'DESIGNER', 17, 'F', TO_DATE('19790221', 'YYYYMMDD'), +0051340.00,+0000500.00,+0001707.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000190', 'JAMES', 'H', 'WALKER', 'D11', '2986', TO_DATE('20040726', 'YYYYMMDD'), 'DESIGNER', 16, 'M', TO_DATE('19820625', 'YYYYMMDD'), +0050450.00,+0000400.00,+0001636.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000200', 'DAVID', ' ', 'BROWN', 'D11', '4501', TO_DATE('20020303', 'YYYYMMDD'), 'DESIGNER', 16, 'M', TO_DATE('19710529', 'YYYYMMDD'), +0057740.00,+0000600.00,+0002217.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000210', 'WILLIAM', 'T', 'JONES', 'D11', '0942', TO_DATE('19980411', 'YYYYMMDD'), 'DESIGNER', 17, 'M', TO_DATE('20030223', 'YYYYMMDD'), +0068270.00,+0000400.00,+0001462.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000220', 'JENNIFER', 'K', 'LUTZ', 'D11', '0672', TO_DATE('19980829', 'YYYYMMDD'), 'DESIGNER', 18, 'F', TO_DATE('19780319', 'YYYYMMDD'), +0049840.00,+0000600.00,+0002387.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000230', 'JAMES', 'J', 'JEFFERSON', 'D21', '2094', TO_DATE('19961121', 'YYYYMMDD'), 'CLERK ', 14, 'M', TO_DATE('19800530', 'YYYYMMDD'), +0042180.00,+0000400.00,+0001774.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000240', 'SALVATORE', 'M', 'MARINO', 'D21', '3780', TO_DATE('20041205', 'YYYYMMDD'), 'CLERK ', 17, 'M', TO_DATE('20020331', 'YYYYMMDD'), +0048760.00,+0000600.00,+0002301.00); INSERT INTO employee (EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES ('000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961', TO_DATE('199

评论收藏

内容反馈