Oracle triggers use

1. Description
1) trigger is a special kind of stored procedure, trigger is usually triggered by the event and cannot accept parameters, memory is called by statement block

2) Trigger classification:
1.DML triggers: Created on a table, raised by DML events
2.instead of triggers: Created on the view and can only be triggered at the row level, in place of actions such as Insert,delete (because Oracle does not directly perform DML operations on views that have more than two tables, the alternative triggers are given. It is a processing method specifically for the operation of the view)
3.DDL Triggers: Creation and modification of database objects when triggering events
4. Database Event trigger: defined in database or schema, triggered by database events

3) Composition:
1. Trigger event: An event DML statement (INSERT, UPDATE, DELETE statement that performs data processing on a table or view) that raises a trigger, DDL statements (such as Create, alter, DROP statements created in the database, modify, delete schema objects), Database system events (such as system startup or exit, exception errors), user events (such as logging on or exiting the database).
2. Trigger time: Whether the trigger is triggered before the triggering event (before) or later (after)
3. Trigger action: What to do after trigger triggers
4. Trigger object: Includes table, view, schema, database. The trigger does not trigger until a trigger event that matches the trigger condition occurs on these objects.
5. Trigger condition: A logical expression is specified by the When clause. Triggering events are triggered automatically only when the value of the expression is true.
6. Trigger frequency: Describes the number of times the action defined within the trigger is executed. That is, statement-level (STATEMENT) triggers and row-level (rows) triggers. (such as delete multiple data, row-level triggers may be executed more than once, statement-level triggers will only fire once)

2. Syntax
1) Description
Different types of triggers such as DML triggers, Instead of triggers, system trigger syntax formats differ greatly

2) General syntax
CREATE [OR REPLACE] Tigger Trigger name trigger Time trigger Event
On table name/view name
[For each row]//plus for each row is a row-level trigger and no overtime is a statement-level trigger
BEGIN
PL/SQL statements
END

create [or Replace] trigger [schema.] Trigger_name
{before | after | instead of}
{Delete [or insert][or update [of Column,... N]}
on [schema.] table_name | View_name
[For each row [when (condition)]]
sql_statement[,... N]

For example:
CREATE OR REPLACE TRIGGER trigger_name
< before | After | Instead of > < inserts | Update | Delete> on table_name
[For each ROW]
When (condition)
DECLARE
BEGIN
END;

3) Instead OF trigger syntax
Grammar:
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD of
{insert| delete| UPDATE [of COLUMN ...]}
[OR {insert| delete| UPDATE [of COLUMN ...]}
On view_name
[Refferencing{old [as] old | NEW [as] new| Parent as Parent}]//Can specify a correlation name, the current default correlation name is old and new,
The//instead of triggers can only be triggered at the row level because there is no need to specify
[When CONDITION]
DECLARE
BEGIN
END;

Description: INSTEAD of DML is used to trigger a view, because views may have multiple tables joined together, so not all junctions can be updated, using the INSTEAD of trigger can do the corresponding operation.

3. Example
To create a test table:
CREATE TABLE "Hnzc". " Triggertest "
(
"ID" VARCHAR2 (BYTE),
"NAME" VARCHAR2 (BYTE),
"Score" number
);
CREATE TABLE TAB1 select * from Triggertest;

1) DML trigger/row level trigger
The triggers are as follows:
CREATE OR REPLACE TRIGGER TRIGGER1
After insert on triggertest//trigger after insert
For each row//row-level trigger
BEGIN
INSERT into Tab1 (id,name) VALUES (' 22 ', ' 33 ');
END;

Execute statement:
INSERT into triggertest (ID) VALUES (' AABBCC ');
Statement execution ends with a new data addition in table TAB1

2) Restrict changes to the table (for example, some tables cannot be modified during non-working hours)
The triggers are as follows:
CREATE OR REPLACE TRIGGER TRIGGER1
After INSERT on Triggertest
For each ROW
BEGIN
IF (To_char (sysdate, ' Day ') in (' Wednesday ', ' Sunday '))
Then Raise_application_error (-20001, ' not working hours, cannot modify form triggertest ');
END IF;
END;

Execute statement:
INSERT into triggertest (ID) VALUES (' AABBCC ');
Today Wednesday thus output results for:
Error starting command execution on line 1:
INSERT into triggertest (ID) VALUES (' AABBCC ')
Error Reporting:
SQL Error: ORA-20001: Not working hours, cannot modify table Triggertest
ORA-06512: In the HNZC. TRIGGER1 ", line 3
ORA-04088: Trigger ' HNZC. TRIGGER1 ' Error during execution

Changes to the table are usually limited to the following (i.e. from Monday to Friday 9--18 can modify the table).
CREATE OR REPLACE TRIGGER TRIGGER1
Before INSERT or DELETE or UPDATE on triggertest
For each ROW
BEGIN
IF (To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday '))
OR (To_char (sysdate, ' hh24:mi ') not between ' 9:00am ' and ' 18:00 ')
Then Raise_application_error (-20001, ' not working hours, cannot modify form triggertest ');
END IF;
END;

3) Add restrictions (such as the inability to change records for an employee)
The trigger is as follows: (the following can only increase the number of months)
CREATE OR REPLACE TRIGGER TRIGGER1
Before INSERT or DELETE or UPDATE on triggertest
For each ROW
When (old.name= ' month's son ')
BEGIN
Case if UPDATING (' score ') then
If:new. Score<:old. Score
Then Raise_application_error (-20001, ' The score of the month can only ascend not to decline ');
END IF;
END case;
END;

The current month's score is 20.
Error when modifying to 10 o'clock
UPDATE "HNZC". " Triggertest "SET score = ' WHERE ROWID = ' Aaadezaapaaaah+aab ' and ORA_ROWSCN = ' 47685303 '
ORA-20001: The moon's score can only be increased and cannot be lowered
ORA-06512: In the HNZC. TRIGGER1 ", line 4
ORA-04088: Trigger ' HNZC. TRIGGER1 ' Error during execution
When modified to 30 o'clock success
UPDATE "HNZC". " Triggertest "SET score = ' WHERE ROWID = ' Aaadezaapaaaah+aab ' and ORA_ROWSCN = ' 47685303 '
Submit Success


4) Call the stored procedure in the trigger
The triggers are:
CREATE OR REPLACE TRIGGER TRIGGER1
Before INSERT or DELETE or UPDATE on triggertest
For each ROW
BEGIN
TESTPRO1 ();
END;

The stored procedure is:
Create or replace
PROCEDURE TESTPRO1 as
BEGIN
Insert into TAB1 (id,name,score) VALUES (' AAA ', ' BBB ', 200);
END TESTPRO1;
Add a piece of data to the TAB1 after execution is complete

5) Cascade Update
The triggers are as follows (the name in the Triggertest table is modified while the name is modified in the TAB1)
Create or replace
PROCEDURE TESTPRO1 as
BEGIN
Insert into TAB1 (id,name,score) VALUES (' AAA ', ' BBB ', 200);
END TESTPRO1;
Execute statement:
Update triggertest set name= ' water ' where name= ' month son ';
Results: In Tab1, the name of the month is also changed to water.


6) Instead OF trigger
Table Student Tabular data is as follows

Create a View Student_view
CREATE OR REPLACE VIEW Studnet_view
As SELECT Classid,avg (score) Average_score from STUDENT
GROUP by CLASSID;
The view data is as follows:

View Student_view do the following:
DELETE from Studnet_view WHERE classid= ' 111 ';
Execution Result:
Error Reporting:
SQL Error: ORA-01732: Invalid data manipulation operation for this view
01732.00000-"Data manipulation operation not legal in this view"

Workaround: Create a instead of view
CREATE OR REPLACE TRIGGER student_view_delete
INSTEAD of DELETE on Studnet_view
For each ROW
BEGIN
DELETE from STUDENT WHERE classid=:old. CLASSID;
END Student_view_delete;

Execute DELETE statement
DELETE from Studnet_view WHERE classid= ' 111 ';
Execution Result: Delete succeeded
1 rows have been deleted.

4. Precautions
1) only DML statements (SELECT, INSERT, UPDATE, DELETE) can be used in the execution portion of the trigger, and DDL statements (CREATE, ALTER, DROP) cannot be used
2) A COMMIT statement cannot be used in a trigger, and the action of the trigger is commit and rollback with the triggering event (Insert,update,delete);
3) The more triggers on a table, the greater the performance impact on the DML operations of the table
4) Maximum trigger is 32K

Oracle triggers use