Shulou(Shulou.com)06/01 Report--

1. An overview of triggers. What is a flip-flop? Triggers are stored in the database and implicitly executed. When a specific event occurs, Oracle automatically executes the corresponding code for the trigger. B, the type of trigger: DML trigger; DDL trigger; instead of trigger; system trigger. C, the composition of the trigger

1. Trigger event: that is, under what circumstances will TRIGGER be triggered.

2. Trigger time: that is, whether the TRIGGER is triggered before (BEFORE) or after (AFTER) the trigger event.

3. The trigger itself: that is, the purpose and intention after the TRIGGER is triggered is exactly what the trigger province is going to do.

4. Trigger frequency: indicates the number of times the action defined in the trigger has been executed.

D. Create the first trigger

Example: after each delete operation, the message prompts: "this is a delete operation!"

SQL >-- when a delete operation is performed on the emp01 table, it displays a message: this is the delete operation SQL > CREATE TRIGGER first_trigger 2 AFTER DELETE 3 ON emp01 4 BEGIN 5 DBMS_OUTPUT.put_line ('this is a delete operation!') ; 6 END; 7 / Trigger createdSQL > SET SERVEROUTPUT ONSQL > DELETE FROM emp01 WHERE empno = 7782; this is a delete operation! 1 row deleted2, DDL trigger a, what is DDL trigger? When creating, modifying, or deleting database objects, it will also cause corresponding trigger operation events, and triggers can be used to monitor the DDL operations of these database objects. B. Syntax format for creating DDL triggers: CREATE [OR REPLACE] TRIGGER trigger name [BEFORE | AFTER | INTEAD OF] [DDL event] ON [DATABASE | SCHEMA] [WHEN trigger condition] [DECLARE] [declaration part of the program;] the code part of the BEGIN program END;/c, DDL event description and trigger timing

D. Code sample 1: disable trigger operations of scott users

Disable DDL operations for scott users

SQL > CREATE OR REPLACE TRIGGER scott_trigger 2 BEFORE DDL 3 ON SCHEMA 4 BEGIN 5 RAISE_APPLICATION_ERROR (- 20005, 'scott users disable all DDL operations'); 6 END; 7 / Trigger createdSQL > CREATE SEQUENCE test_seq CREATE SEQUENCE test_seqORA-00604: recursive SQL level 1 error ORA-20005: scott user forbids all DDL operations ORA-06512: note in line 2: RAISE_APPLICAITON_ERROR is an exception handling used for testing, which can transfer application-specific errors from the server to the client application (SQLPlus on other machines or other foreground development languages). Its stored procedure has two parameters. Such as: RAISE_APPLICAITON_ERROR (error_number_in IN NUMBER, error_msg_in IN VARCHAR2) -- the length of error_msg_in cannot exceed 2k. When the length exceeds 2k, e will be intercepted. Code example 2: log database object operations.

Break it down into three steps:

1, create the database object DDL operation log table; 2, create the trigger to record the database object DDL operation; 3, test.

SQL > connect system/02000059 as sysdba;Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as system AS SYSDBASQL > CREATE TABLE object_log (--create database object DDL operation log table 2 logid number constraint pk_logid primary key, 3 operatedate date not null, 4 objecttype varchar2 (50) not null, 5 objectowner varchar2 (50) not null); Table createdSQL > CREATE SEQUENCE object_log_seq Sequence createdSQL > CREATE OR REPLACE TRIGGER ojbect_trigger-create trigger 2 AFTER CREATE OR DROP OR ALTER 3 ON DATABASE 4 BEGIN 5 INSERT INTO object_log (logid, operatedate, objecttype, objectowner) VALUES (object_log_seq.nextval, sysdate, ora_dict_obj_type, ora_dict_obj_owner) to record DDL operations on database objects; 6 END; 7 / Trigger createdSQL > CREATE SEQUENCE test_seq;-Test Sequence createdSQL > select * from object_log LOGID OPERATEDATE OBJECTTYPE OBJECTOWNER -- 1 2017-12-21 SEQUENCE SYS Note: the example of this DDL trigger event uses two property functions (not previously used): ORA_DICT_OBJ_OWNER: the user of the database object that triggered the DDL ORA_DICT_OBJ_TYPE: the type of database object that triggered the DDL. 3. DML trigger a, what is a DML trigger? A DML trigger is a trigger based on a DML operation. B, the role of DML trigger DML trigger can be used to achieve data security protection, data audit, data integrity, reference integrity, data replication and other functions. C, DML trigger types include statement trigger and row trigger.

1. statement trigger: execute once before or after the specified action statement, no matter how many rows are affected by the statement. (for rows)

2. Row trigger: every record that triggers the statement is triggered. In row-level triggers, old and: new pseudo-record variables are used to identify the status of the value. (for tables): old indicates the value of the row before the operation;: new indicates the value of the row after the operation of the row. D. Syntax format for creating DML triggers CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF column name]} ON table name [FOR EACH ROW [WHEN (condition)]) PLSQL block e, example 1: data security protection (data security check) example: it is forbidden to change the data of emp table on rest day (Saturday or Sunday).

Analysis: 1, use to_char (sysdate, 'day') function; 2, use statement trigger.

SQL > CREATE OR REPLACE TRIGGER emp_trigger 2 BEFORE INSERT OR UPDATE OR DELETE 3 ON emp 4 BEGIN 5 IF to_char (sysdate, 'day') IN (' Saturday', 'Sunday') THEN 6 RAISE_APPLICATION_ERROR (- 20006, 'employee information cannot be changed on rest days!') ; 7 END IF; 8 END; 9 / Trigger createdSQL > DELETE FROM emp WHERE empno = 7369 * delete FROM emp WHERE empno = 7369ORA-20006: employee information cannot be changed on rest days! ORA-06512: error in "SCOTT.EMP_TRIGGER", line 3ORA-04088: trigger 'SCOTT.EMP_TRIGGER' execution f, example 2: implement data audit example: audit employee information table data changes, audit deletion time, and deleted employee name.

Use the SQL WINDOW window: (step by step)

-- create audit table CREATE TABLE delete_emp_audit (name VARCHAR2 (10), delete_time DATE);-- create trigger CREATE OR REPLACE TRIGGER del_emp_triggerAFTER DELETE ON empFOR EACH ROWBEGIN INSERT INTO delete_emp_audit VALUES (: old.ename, SYSDATE);-- insert the name and current time of the deleted employee END;-- test DELETE FROM emp WHERE empno = 7499 select * FROM delete_emp_audit In SQL and PLSQL statements, old and new pseudo-record variables need a colon: In restrictive statements such as WEHN, there is no need to add a colon:. G, example 3: achieving data integrity (data validation) data integrity is used to ensure that the data meets business logic or enterprise rules. To achieve data integrity preferred constraints, constraints can not be achieved, you can use triggers to achieve data integrity.

Example: for example, the salary of the employee should not be lower than the original salary, and the salary should not exceed 50% of the original salary.

SQL > CREATE OR REPLACE TRIGGER tr_check_sal 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (new.sal

Old.sal * 1.5) 5 BEGIN 6 RAISE_APPLICATION_ERROR (- 20028, 'wages go up without falling, and the rate of increase cannot exceed 50%'); 7 END; 8 / Trigger createdSQL > UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902 UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902ORA-20028: wages go up without falling, and the increase cannot exceed 50%ORA-06512: in "SCOTT.TR_CHECK_SAL", line 2ORA-04088: trigger 'SCOTT.TR_CHECK_SAL' error h, example 4: achieve referential integrity (such as cascading updates) in order to achieve cascading deletion, you can specify the ON DELETE CASCADE keyword when defining external key constraints. However, cascading updates can not be achieved by using constraints. In order to achieve cascading updates, triggers are needed.

Example: cascading updates the primary key of the DEPT table and the foreign key column of the EMP table.

CREATE OR REPLACE TRIGGER upd_cascade_triggerAFTER UPDATE OF deptnoON deptFOR EACH ROW BEGIN UPDATE emp SET deptno =: new.deptno WHERE deptno =: old.deptno;END;-- Test UPDATE dept SET deptno = 50 WHERE deptno = 10 * select deptno, ename FROM emp WHERE deptno = 50 * * 4, INSTEAD OF flip-flop a, what is INSTEAD OF flip-flop (alternative trigger)? Applicable to trigger b on the view, why use INSTEAD OF triggers? INSET, UPDATE, and DELETE operations can often be performed on a simple view. However, there are restrictions when performing INSET, UPDATE, and DELETE operations on complex views. So in order to perform DML operations on these complex views, you need to create alternative triggers. C. The restriction alternative triggers of INSTEAD OF triggers apply only to views. Alternative triggers cannot specify BEFORE and AFTER options. You cannot create an alternative trigger on a view with the WITH CHECK OPTION option.

Alternative triggers must contain the FOR EACH ROW option.

D, sample code

1. Create an emp_dept view:

SQL > CREATE OR REPLACE VIEW emp_dept 2 AS 3 SELECT d.deptno, d.dname, e.empno, e.ename 4 FROM dept d, emp e 5 WHERE d.deptno = e.deptnotion View created

2. There was an error inserting data into the emp_dept view when no alternative trigger was created:

SQL > INSERT INTO emp_dept VALUES (50, 'DEVELOPMENT', 2222,' ALICE'); INSERT INTO emp_dept VALUES (50, 'DEVELOPMENT', 2222,' ALICE') ORA-01779: columns corresponding to non-key save tables cannot be modified

3. Create an alternative trigger for emp_dept views (complex views):

CREATE OR REPLACE TRIGGER instead_of_triggerINSTEAD OF INSERTON emp_deptFOR EACH ROWDECLARE v_temp INT;BEGIN SELECT COUNT (*) INTO v_temp FROM dept WHERE deptno =: new.deptno; IF v_temp = 0 THEN INSERT INTO dept (deptno, dname) VALUES (: new.deptno,: new.dname); END IF; SELECT COUNT (*) INTO v_temp FROM emp WHERE empno =: new.empno IF v_temp = 0 THEN INSERT INTO emp (empno, ename, deptno) VALUES (: new.empno,: new.ename,: new.deptno); END IF;END

4. Insert the emp_dept view:

SQL > INSERT INTO emp_dept VALUES (50, 'DEVELOPMENT', 2222,' ALICE'); 1 row inserted SQL > SELECT * FROM EMP WHERE empno = 2222 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-2222 ALICE 50 SQL > SELECT * FROM DEPT WHERE deptno = 50 DEPTNO DNAME LOC-50 ACCOUNTING NEW YORK SQL > SELECT * FROM emp_dept WHERE empno = 2222 DEPTNO DNAME EMPNO ENAME-50 ACCOUNTING 2222 ALICE5, system trigger a, what is a system trigger? A system trigger is a trigger triggered by a specific system event. (it should be noted that system triggers are created by system users.)

System events are database events related to routines or scenarios, including STARTUP, SHUTDOWN, DB_ROLE_CHANGE, and SERVERERROR events.

1. STARTUP event triggers are triggered after the database is started.

2. The SHUTDOWN event trigger is triggered before the data is closed.

3. DB_ROLE_CHANGE event triggers are triggered when the database is opened for the first time after a role change.

4. SERVERERROR event triggers are triggered when an Oracle error occurs.

B, sample code

1. Create a database system EVENT_TALBE event table that records the occurrence:

CREATE TABLE event_table (event VARCHAR2 (50), event_time date)

2. Create a system trigger:

SQL > CREATE OR REPLACE TRIGGER startup_trigger 2 AFTER STARTUP ON DATABASE 3 BEGIN 4 INSERT INTO event_table VALUES (ORA_SYSEVENT, SYSDATE); 5 END; 6 / Trigger created

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.