Oracle pl/SQL trigger Programming

1. Basic Concepts
Two features: complete the constraints of complex business rules that are difficult to complete due to database integrity constraints; monitor various database operations to implement audit functions.
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, starting or shutting down the database)
Trigger event: the brackets in the above trigger are all trigger events.
Trigger condition: WHEN clause
Trigger objects: including tables, views, modes, and databases.
Trigger operation: The program automatically executed by the trigger.
Trigger time: time when the trigger is executed relative to the action, BEFORE/AFTER
Condition predicates: INSERTING (when the trigger event is INSERT, it is true), UPDATING, DELETING
Trigger subtype: Row trigger and statement trigger. new and old tables are triggered.
2. Create a trigger
Copy codeThe Code is as follows:
Create or replace trigger <TRIGGER Name>

Trigger Condition
Trigger body
Copy codeThe Code is as follows:
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 Handling is also available 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
Automatic Execution
Copy codeThe Code is as follows:
Create trigger my_trigger1
After insert or UPDATE or DELETE on TEACHERS
FOR each row;
DECLARE
Info CHAR (10 );
BEGIN
IF inserting THEN -- IF the insert operation is performed
Info: = 'insert ';
ELSIF updating THEN -- if you want to modify
Info: = 'update ';
ELSE -- if the deletion operation is performed
Info: = 'delete ';
End if;
Insert into SQL _INFO VALUES (info); -- Record the operation information
END my_trigger1;

4. delete a trigger
Copy codeThe Code is as follows:
Drop trigger my_trigger;