ORACLE PL and SQL Trigger programming article

1. Basic concepts
Two functions: to complete the constraints of complex business rules that are difficult to complete by the integrity constraints of the database; Monitor database operation and implement audit function.
Triggers are: DML triggers (triggered when performing DML actions on a table or view), INSTEAD OF triggers (defined only on a view instead of an actual action statement), and system triggers (triggered when performing an action on a database system, such as DDL statements, starting or closing a database, etc.)
Trigger event: Everything in parentheses in the above trigger is a trigger event.
Trigger condition: WHEN clause
Trigger objects: Include tables, views, schemas, databases.
Trigger action: A program that triggers automatic execution.
Trigger timing: The time the trigger executes relative to the operation, BEFORE/AFTER
Conditional predicates: INSERTING (true if trigger event is INSERT), UPDATING, DELETING
Trigger subtypes: row trigger and statement trigger, new and old tables in trigger.
2. Create triggers

 
CREATE OR REPLACE TRIGGER< The trigger name > 

The trigger condition
The trigger body

 
CREATE TRIGGER my_trigger -- define 1 A trigger my - trigger 
BEFORE INSERT or UPDATE of TID,TNAME on TEACHERS 
FOR each row 
WHEN(new.TNAME='David') -- this 1 Part of it is the trigger condition  
DECLARE -- The following 1 The 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 handling is also used here  
WHEN INSERT_EXIST_TEACHER THEN 
INSERT INTO ERROR(TID,ERR) 
VALUES(teacher_id . 'the teacher already exists!'); 
END my triqqer; 

3. Execute triggers
automated

 
CREATE TRIGGER my_trigger1 
AFTER INSERT or UPDATE or DELETE on TEACHERS 
FOR each row ;  
DECLARE 
info CHAR(10) ;  
BEGIN 
IF inserting THEN -- If you do an insert  
info:='INSERT'; 
ELSIF updating THEN -- If you modify the operation  
info:='Update'; 
ELSE-- If the delete operation is performed  
info:='Delete'; 
END IF; 
INSERT INTO SQL_INFO VALUES(info); -- Record the operation information  
END my_trigger1; 

4. Delete triggers

 
DROP TRIGGER my_trigger;