Oracle Database PL/SQL triggers

Oracle Database PL/SQL trigger 7) trigger www.2cto.com 1. two basic concepts: complete the constraints of complex business rules that are difficult to complete due to the integrity constraints of the database; monitor various operations of the database to implement the audit function. Triggers are divided into: DML triggers (triggered when DML operations are performed on tables or views), INSTEAD triggers (defined only on views, instead of actual operation statements ), system trigger (triggered when operating the database system, such as DDL statements, start or close the database): the brackets in the above trigger are all trigger events. Trigger condition: WHEN clause trigger object: including table, view, mode, and database.
Trigger operation: The program automatically executed by the trigger. Trigger time: the time when the trigger is executed relative to the operation. The condition predicate BEFORE/AFTER: INSERTING (the trigger event is true when the trigger event is INSERT), UPDATING, DELETING trigger subtype: Row trigger and statement trigger, new and old tables in the trigger. 2. create trigger www.2cto.com create or replace trigger <trigger Name> TRIGGER condition trigger create trigger my_trigger -- Define a TRIGGER my-trigger before insert or UPDATE of TID, TNAME on teachers for each row WHEN (new. TNAME = 'David ') -- this part is the trigger condition DECLARE -- the following part is the trigger body teacher_id TEACHERS. TID % TYPE; INSERT_EXIST_TEACHER EXCEPTION; begin select tid into teacher_id from teachers where tname = new. TNAME; RAISE INSERT_EXIST_TEACHER; EXCEPTION -- EXCEPTION Here, WHEN INSERT_EXIST_TEACHER then insert into error (TID, ERR) VALUES (teacher_id, 'The teacher already exists! '); END my triqqer; 3. execute the TRIGGER to automatically execute create trigger my_trigger1 after insert or UPDATE or DELETE on teachers for each row; DECLARE info CHAR (10); begin if inserting THEN -- if insert operation info: = 'insert'; ELSIF updating THEN -- IF the modification operation info: = 'update'; ELSE -- IF the deletion operation info: = 'delete'; end if; insert into SQL _INFO VALUES (info); -- Record the operation information END my_trigger1; 4. delete trigger drop trigger my_trigger;