Oracle learning notes thirteen triggers, oracle learning notes

Oracle learning notes thirteen triggers, oracle learning notes
Introduction A trigger is a stored procedure that is automatically executed when a specific event occurs. A specific event can be an updated DML statement and DDL statement, and the trigger cannot be explicitly called. Trigger functions: 1. automatically generate data 2. Customize complex security permissions 3. provide audit and logging 4. Enable the syntax of complex business logic creation triggers

CREATE [OR REPLACE] TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OF[INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE]ON table_or_view_name[REFERENCING {OLD [AS] old / NEW [AS] new}][FOR EACH ROW][WHEN ( condition ) ]pl/sql_block;

: New -- reference the latest column value;
: Old -- refers to a column value that references the past. These two variables exist only when the keyword "foreach row" is used.

There are two update statements, while insert only has: new and delect only has: old;

 

A trigger consists of three parts: the trigger Statement (event) defines the conditions for activating the DML event of a trigger and the DDL event trigger to limit the execution of the trigger. The condition must be true before the trigger operation (subject) can be activated) contains some SQL statements and code that run the trigger statement when the trigger statement is issued and the trigger limit value is true.

Create or replace trigger trig_sal after update of empsal ON salary_records -- activate the trigger after updating the emp_sal column...

Trigger restrictions

... For each row when (NEW. empsal> OLD. empsal) -- trig_sal trigger DECLARE Sal_diff NUMBER ;...

 

Trigger operation

... BEGIN sal_diff: =: NEW. empsal-: OLD. empsal; -- if the conditions in the WHEN clause are met, the Code DBMS_OUTPUT.PUT_LINE ('salary difference: 'sal _ diff) in the begin block will be executed; END;

 

 

How BEFORE triggers work to create triggers

Create or replace trigger aiu_itemfileAFTER INSERTON itemfileFOR each rowbegin if (: NEW. qty_hand = 0) THEN DBMS_OUTPUT.PUT_LINE ('Warning: records inserted, but the number is 0'); ELSE DBMS_OUTPUT.PUT_LINE ('records inserted '); end if; END;

View table triggers

select * from all_triggers where table_name =upper('tbname')   

 

Create or replace trigger TR_SEC_EMPBEFOR insert or update or delete on EMP2BEGIN IF TO_CHAR (SYSDATE, 'dy ', 'nls _ date_language = AMERICAN') IN ('sat ', 'sun ') THEN RAISE_APPLICATION_ERROR (-20002, 'do not modify data! '); End if; END;

 

In general, the salary is adjusted upwards. Do you need to write a trigger to prohibit lower wages?

Create or replace trigger guo_trigger ---- create trigger before update on emp2 ---- specify trigger time for each row ---- row trigger ID when (new. sal <old. sal) ---- trigger condition begin raise_application_error (-20500, 'salary cannot be reduced for employees'); end;

 

Trigger types:

 

 

DDL triggers execute database-level triggers when DDL statements are executed in the mode. When system events such as opening, closing, logging on to and exiting the database occur, DML triggers execute statements when running DML statements on tables or views. no matter how many rows are affected, only one row-Level Trigger is executed once for each row modified by the DML statement. The instead of trigger is used to view that users cannot directly modify the DML statement. Summary: trigger composition: l trigger event: events that trigger a trigger. For example, DML statements (INSERT, UPDATE, and DELETE statements that perform data processing on tables or views), DDL statements (such as CREATE, ALTER, and DROP statements that CREATE, modify, and DELETE schema objects in the database) database System events (such as system startup or exit, abnormal errors), user events (such as logon or database exit ). L TRIGGER time: whether the TRIGGER is triggered BEFORE or AFTER the TRIGGER event, that is, the TRIGGER event and the TRIGGER operation sequence. L TRIGGER operation: the purpose and intent of the TRIGGER after it is triggered, which is exactly what the TRIGGER is going to do. For example, PL/SQL block. L trigger objects: including tables, views, modes, and databases. Only trigger events that meet the trigger conditions on these objects can be triggered. L trigger condition: A logical expression is specified by the WHEN clause. The trigger is automatically executed only when the value of this expression is TRUE. L trigger frequency: the number of times the action defined in the trigger is executed. STATEMENT-level (STATEMENT) triggers and ROW-level (ROW) triggers.

STATEMENT-level triggers: When a trigger event occurs, the trigger is executed only once. ROW triggers: When a trigger event occurs, the trigger is executed once for each row of data affected by this operation.

The trigger does not accept parameters. L a table can have up to 12 triggers, but only one trigger of the same time, event, and type can have one. There must be no conflict between triggers. L The more triggers on a table, the greater the impact on the performance of DML operations on the table. L The maximum trigger size is 32 KB. If necessary, you can first create a process and then use the CALL statement in the trigger to CALL it. L only DML statements (SELECT, INSERT, UPDATE, and DELETE) can be used for trigger execution, and DDL statements (CREATE, ALTER, and DROP) cannot be used ). L The trigger cannot contain transaction control statements (COMMIT, ROLLBACK, SAVEPOINT ). Because the trigger is part of the trigger statement, when the trigger statement is submitted or rolled back, the trigger is also submitted and rolled back. L no process or function called in the trigger body can use transaction control statements. L you cannot declare any Long or blob variables in the trigger body. The new value and old value cannot be added to any long or blob columns in the table.

L syntax formats and functions OF different types OF triggers (such as DML triggers, instead of triggers, and system triggers) are significantly different.

 

Row-level triggers

Create table TEST_TRG (id number, NAME VARCHAR2 (20); create sequence SEQ_TEST;
Create or replace trigger BI_TEST_TRG before insert or update of id on TEST_TRG for each rowbegin -- function UPDATING, DELETING determines which operation triggers the TRIGGER. if inserting then select SEQ_TEST.NEXTVAL INTO: NEW. id from dual; ELSE RAISE_APPLICATION_ERROR (-20020, 'ID value cannot be updated! '); End if; END;

 

Back up employee salaries before modifying data

CREATE OR REPLACE TIGGER TR_SAL_BACK AFFTER UPDATE OF SAL ON EMP2 FOR EACH ROW DECLARE 　　V_TEM INT ;BEGIN 　　SELECT COUNT(*) INTO V_TEMP FROM NEW_BACK WHERE ENAME =:OLD.ENAME;　　IF V_TEMP =0 THEN 　　　　INSERT INTO NEW_BACK VALUES (:OLD.ENAME,:OLD.SAL, :NEW.SAL,SYSDATE);　　ELSE 　　　　UPDATE NEW_BACK VALUES  SET OLDSAL=:OLDSAL, NEWSAL=:NEW.SAL,TIME=SYSDATE WHERE NAME=:OLD.ENAME;　　END IF; END;

 

Create a trigger. When a record is deleted from the employee table emp, write the deleted record to the employee table deletion log table.

Create table emp_his as select * from emp where 1 = 2; create or replace trigger tr_del_emp before delete -- specify the TRIGGER time to trigger on scott BEFORE the DELETE operation. emp for each row -- indicates that the ROW-Level Trigger BEGIN is created -- Insert the data before modification to the log record table del_emp FOR supervision. Insert into emp_his (deptno, empno, ename, job, mgr, sal, comm, hiredate) VALUES (: old. deptno,: old. empno,: old. ename,: old. job,: old. mgr,: old. sal,: old. comm,: old. hiredate); END; DELETE emp WHERE empno = 7788; drop table emp_his; drop trigger del_emp;

Statement-level triggers

Create or replace trigger trgdemo after insert or update or delete on order_masterBEGIN if updating then updated ('data in ORDER_MASTER updated '); elsif deleting then DBMS_OUTPUT.PUT_LINE ('data in ORDER_MASTER deleted '); elsif inserting then DBMS_OUTPUT.PUT_LINE ('data inserted in ORDER_MASTER '); end if; END;

 

Row-level and statement-based differences between a row-based trigger and a statement-based trigger are as follows: a row-based trigger requires that when a DML statement is executed to affect multiple rows of data in the database, for each row of data, each trigger is activated once as long as they meet the trigger constraints. The statement trigger uses the entire statement operation as the trigger event. when it meets the constraints, the trigger is activated once. If the for each row option is omitted, BEFORE and AFTER triggers are statement triggers, while instead of triggers can only be ROW triggers. Instead of trigger

Create or replace trigger upd_ord_view instead of update on ord_view for each rowbegin update order_master SET vencode =: NEW. vencode WHERE orderno =: NEW. orderno; DBMS_OUTPUT.PUT_LINE ('activated trigger '); END;

 

Mode trigger

CREATE TABLE dropped_obj 
(　　obj_name VARCHAR2(30),　　obj_type VARCHAR2(20),　　drop_date DATE
);
CREATE OR REPLACE TRIGGER log_drop_obj　　AFTER DROP ON SCHEMABEGIN　　INSERT INTO dropped_obj　　VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;

 

Enable, disable, and delete triggers

ALTER TRIGGER aiu_itemfile DISABLE;

ALTER TRIGGER aiu_itemfile ENABLE;

 

Delete trigger

DROP TRIGGER aiu_itemfile;

View trigger information. The USER_TRIGGERS data dictionary view contains trigger information.

SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME='EMP';

SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';