PL/SQL 07 trigger Trigger

--Trigger

There are three types of triggers:
Data manipulation triggers
Data verification with before Trigger
Cascade operations with AFTER triggers
Statement triggers restrict the operation of data and record operation logs
Instead OF triggers (when DML operations are not allowed for views only)
Data definition triggers
Monitor some important actions of users in the database
System triggers

--Limitations of triggers

Transaction control statements should not be used
COMMIT, ROLLBACK, savepoint

Any procedure or function that is called by a trigger cannot use a transaction control statement

Cannot declare any long or long raw variables

Limited access to tables

Change chart
Table that is being modified by the DML statement, that is, the table that defines the trigger

restriction table
Some tables have a declaration with referential integrity restrictions when they are created
Primary key and foreign key

Limitations in trigger body
No change tables can be read or modified
You cannot read or modify the primary key, unique value column, foreign key column of a restricted table

--Data manipulation triggers

Create or replace trigger trigger name
Before/after Insert/update/delete
On table name/view name
For each row
Declare
variable definition;
Begin
Code
End

--for each row is a row-level trigger and is removed as a statement-level trigger

: New. field data after the operation of the content
: Old. Content before field data operation

--Prohibit the addition of the computer principle to the T_score table results

Create or Replace Trigger Trg_insert_sco
Before insert
On T_score
For each row
Declare
Coursename T_course.coursename%type;
Begin
Select Coursename
Into Coursename
From T_course
where Courseno=:new.courseno;
If coursename= ' microcomputer principle ' then
Raise_application_error (-20001, ' The course has completed the exam and cannot add a record of achievement ');
End If;
End

--When a record is added to the student table, the student is automatically added to the advanced math midterm and the score is set to null

Create or Replace Trigger Trg_insert_stu
After insert
On T_student
For each row
Begin
INSERT INTO T_score
VALUES (: New.stuno, ' Midterm ', (select Courseno
From T_course
Where coursename= ' high data '), NULL);
End

--Statement Trigger
--Prohibit the operation of the score table from 8:00 to 18:00

Create or Replace Trigger Trg_ope_sco
Before insert or UPDATE or delete
On T_score
Begin
if (To_char (sysdate, ' hh24:mi ') not between ' 8:00am ' and ' 18:00 ')
Then
Raise_application_error (-20004, ' can no longer operate on the T_score table during non-working hours ');
End If;
End

--instead of Triggers
--for views that contain joins or subqueries, DML operations are generally not possible and can be implemented with instead OF triggers

Create or replace trigger trigger name
Instead of Insert/update/delete
on view name
For each row
Declare
variable definition;
Begin
Code
End

Create or Replace Trigger Trg_update_vw_cou_tea
Instead of update
On Vw_cou_tea
For each row
Declare
TNO T_teacher.teano%type;
Begin
Select Teano
Into TNO
From T_teacher
where Teaname=:new.teaname;
Update T_course
Set Teano=tno
where Coursename=:old.coursename;
End

--Data definition language triggers

Create or replace trigger trigger name
Before/after User Events
On Database|schema
Declare
variable definition;
Begin
Code
End

--user events including Create, alter, DROP, user logon login, etc.

--Prevent users from deleting database objects

Create or Replace Trigger Trg_drop
Before drop
On schema
Begin
Raise_application_error (-20005, ' cannot be deleted ');
End

--Trigger (OEM)

Create Trigger Bmbtrigger
Before insert or UPDATE or delete
On BMB
Begin
if (To_char (sysdate, ' Day ')) = ' SUN '
Then
Raise_application_error ( -20600, ' Can not be Update table on weekend. ');
End If;
End

Create or Replace Trigger Gzbtrigger
After insert or update or delete
On GZB
DECLARE suminconme number
Begin
Select sum (income) to sumincome from Gzb;
If sumincome<50000
Then
Raise_application_error (-20001, ' income below 20,000 ');
End If;
End

PL/SQL 07 trigger Trigger