Introduction to ORACLE Pl/sql Trigger programming _oracle

1. Basic Concepts
Two functions: To complete the constraints of complex business rules that are difficult to complete by database integrity constraints, to monitor various operations of the database, and to implement audit functions.
Triggers are divided into: DML triggers (triggered when a DML operation is performed on a table or view), INSTEAD of triggers (defined only on the view, alternative to the actual action statement), system triggers (triggers when operating on the database system, such as DDL statements, starting or shutting down the database, and so on)
Trigger event: The contents of parentheses in the above triggers are triggered events.
Trigger condition: When clause
Trigger objects: Include tables, views, schemas, databases.
Trigger action: The program that the trigger automatically executes.
Trigger timing: Trigger relative to the time the execution of the operation, Before/after
Conditional verb: inserting (trigger event is true when insert), updating,deleting
Trigger subtype: Row trigger and statement trigger, the new and old table in the trigger.
2. Create triggers

Copy Code code as follows:

CREATE OR REPLACE trigger< trigger name >

Trigger condition
Trigger Body

Copy Code code 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 is part of the trigger condition
DECLARE--The following section 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 can also be used here
When Insert_exist_teacher THEN
INSERT into ERROR (Tid,err)
VALUES (teacher_id, ' The teacher already exists! ');
End my triqqer;

3. Execute Trigger
Automatic execution

Copy Code code as follows:

CREATE TRIGGER My_trigger1
After inserts or UPDATE or DELETE on TEACHERS
For each row;
DECLARE
Info CHAR (10);
BEGIN
If inserting THEN--if the insert operation
info:= ' INSERT ';
elsif updating THEN--If you are doing a modification
info:= ' Update ';
else--If a delete operation
info:= ' Delete ';
End IF;
INSERT into Sql_info VALUES (INFO); --Record This operation information
End My_trigger1;

4. Delete triggers

Copy Code code as follows:

DROP TRIGGER My_trigger;