Pl-sql Trigger



Pl-sql Trigger

Triggers are a technique that is provided by many relational database systems. In Oracle systems, trigger-like procedures and functions have a PL/SQL block that declares, executes, and processes exceptions.

① Trigger Type
Triggers are stored as separate objects in the database, unlike stored procedures, where stored procedures start or run directly through other programs, and triggers are run by an event.
That is, a trigger is automatically run implicitly when an event occurs. Also, the trigger cannot receive parameters. So running the trigger is called trigger or ignition (firing).
An Oracle event refers to an insert, update, and delete operation on a table in a database or a similar operation on a view.
Oracle extends the capabilities of triggers to trigger Oracle, such as database startup and shutdown.
1.DML Trigger
Oracle can trigger on DML statements, trigger before or after DML operations, and trigger on each row or statement operation.
2. Alternative triggers
Because in Oracle, it is not possible to operate directly on a view that is established by more than two tables. Therefore, an alternative trigger is given.
3. System triggers
It can be triggered in the event of an Oracle database system, such as the startup and shutdown of an Oracle system.

② triggers consist of:
 Trigger event: in which case the trigger is triggered; For example: INSERT, UPDATE, DELETE.
 Trigger time: That is, whether the trigger is triggered before the triggering event (before) or later (after), that is, the sequence of actions that triggered the event and the trigger.
The  trigger itself: that is, the purpose and intent of the trigger after it is triggered is exactly what the trigger itself does. For example, PL/SQL blocks.
 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.
Statement-level (STATEMENT) Trigger: The trigger executes only once when a trigger event occurs;
Row-level triggers: A trigger is executed once for each row of data affected by the operation when a triggering event occurs.

③ Creating triggers
The general syntax for creating triggers is:
Create[or REPLACE] TRIGGER trigger_name
{before| After}
{insert| delete| Update[of column [, Column ...]}
On[schema.] table_name
[For each ROW]
[When condition]
Begin
Trigger_body;
End
which
Before and after indicate that the triggering sequence of the trigger is pre-trigger and post-trigger, respectively,
The pre-trigger is the trigger that is currently created before the triggering event is executed, and the trigger is triggered after the trigger event is executed.
The For each row option describes the trigger as a row trigger.
The difference between a row trigger and a statement trigger is that a row trigger requires that when a DML statement is manipulated to affect multiple rows of data in a database, for each row of data,
The trigger is activated as long as they conform to the trigger constraint;
A statement trigger activates a trigger when it conforms to the constraint, as the entire statement operation acts as a trigger event.
When the For each ROW option is omitted, the before and after triggers are statement triggers, while the instead OF triggers are row triggers.
The When clause describes the triggering constraint condition. When Condition is a logical expression, it must contain a correlation name, not a query statement, or a PL/SQL function.
The trigger constraint specified by the When clause can only be used in before and after row triggers, not in instead of row triggers and other types of triggers.

The stored procedure to execute when a base table is modified (INSERT, UPDATE, DELETE) is automatically triggered when executed based on the base table changes it is attached to, so it is not application-independent.
Database triggers are used to guarantee the consistency and completeness of data.

You can create up to 12 types of triggers per table, which are:
Before INSERT
Before INSERT for each ROW
After INSERT
After INSERT for each ROW

Before UPDATE
Before UPDATE for each ROW
After UPDATE
After UPDATE for each ROW

Before DELETE
Before DELETE for each ROW
AfterDelete
After DELETE for each ROW

④ Trigger Trigger Order
1. Execute before statement level trigger;
2. For each line affected by the statement:
 executing before row-level triggers
 Executing DML statements
 executing after row level triggers
3. Executing after-statement-level triggers

⑤ creating a DML trigger
A trigger name can have the same name as a table or procedure, but the trigger name cannot be the same in a pattern.
Limitations OF Triggers
The character length of create trigger statement text cannot exceed 32KB;
The SELECT statement in the  trigger body can only be a select ... Into ... Structure, or the SELECT statement used to define the cursor.
Database transaction CONTROL Statement commit cannot be used in  triggers; ROLLBACK, Svaepoint statement;
 a procedure or function called by a trigger cannot also use a database transaction control statement;

Issue: When a trigger is triggered, the value of the column in the record being inserted, updated, or deleted is used, and sometimes the pre-and post-column values are used.
Implementation:: The NEW modifier accesses the value of the column after the operation is completed
: Old modifier to access the value of the top of the operation
Attribute INSERT UPDATE DELETE
Old NULL effectively valid
NEW valid valid NULL
Cases
Create or Replace Trigger Hello_trigger
After
Update on employees
For each row
Begin
Dbms_output.put_line (' Hello ... ');
Dbms_output.put_line (' old.salary: ' | |: old.salary| | ', New.salary ' | |:new.salary);
End

Then execute: Update employees Set salary = salary + 1000;

⑥ Create alternative (INSTEAD of) triggers
The general syntax for creating triggers is:
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD of
{INSERT | DELETE | UPDATE [Ofcolumn [, Column ...]}
On[schema.] View_name
[For each ROW]
[Whencondition]
Begin
Trigger_body;
End
which
The INSTEAD of option enables Oracle to activate triggers without triggering events.
Instead OF triggers can only be established on view and object views, and instead OF triggers cannot be established on tables, schemas, and databases.
The For each row option describes the trigger as a row trigger.
The difference between a row trigger and a statement trigger is that a row trigger requires that when a DML statement is manipulated to affect multiple rows of data in a database, the trigger is activated for each row of data, as long as they conform to the trigger constraint;
A statement trigger activates a trigger when it conforms to the constraint, as the entire statement operation acts as a trigger event.
When the For each ROW option is omitted, the before and after triggers are statement triggers, while the instead OF triggers are row triggers.
The When clause describes the triggering constraint condition. When Condition is a logical expression, it must contain a correlation name, not a query statement, or a PL/SQL function.
The trigger constraint specified by the When clause can only be used in before and after row triggers, not in instead of row triggers and other types of triggers.
Instead_of is used to trigger a DML on a view, and since views are likely to be joined by multiple tables, not all junctions are updatable.
However, you can perform the update in the way that you want,
For example, the following conditions:
CREATE OR REPLACE VIEW Emp_view
As
SELECT Deptno, COUNT (*) Total_employeer, sum (SAL) total_salary
From the EMP GROUP by Deptno;

The direct deletion in this view is illegal:
Sql>delete from Emp_view WHERE deptno=10;
DELETE from Emp_view WHERE deptno=10
*
ERROR on line 1th:
ORA-01732: Illegal data manipulation operation for this view

However, you can create a instead_of trigger to perform the required processing for the delete operation, deleting all datum rows in the EMP table:

CREATE OR REPLACE TRIGGER emp_view_delete
INSTEAD of DELETE on Emp_view for each ROW
BEGIN
DELETE from emp WHERE deptno=: Old.deptno;
END Emp_view_delete;

DELETE from Emp_view WHERE deptno=10;

⑦ creating a System Event trigger
System event triggers provided by Oracle can be triggered on DDL or database systems. DDL refers to the data definition language, such as Create, alter, and drop.
Database system events include startup or shutdown of the database server, login and exit of the user, database service error, and so on. The syntax for creating a system trigger is as follows:

1. The general syntax for creating triggers is:
CREATE OR REPLACE TRIGGER [Sachema.] Trigger_name
{before| After}
{ddl_event_list | database_event_list}
on {DATABASE | [Schema.] SCHEMA}
[When_clause]
Begin
Trigger_body;
End
Where: ddl_event_list: One or more DDL events, between events with or separate; Database_event_list: one or more database events, separated by or between events;
System event triggers can be built on a single schema and on the entire database.
When built on a schema, only the DDL actions of the user specified by the schema and the errors they cause are activated by default to the current user mode.
When built on top of the database, the DDL operations of all users of the database and the errors they cause, as well as the startup and shutdown of the database, activate triggers.
To establish a trigger on top of the database, the user is required to have Administer database trigger permissions.

⑧ Delete trigger:
DROP TRIGGER trigger_name;
When you delete a trigger name in another user mode, you need to have the drop any trigger system permission,
When you delete a trigger that is built on a database, the user needs to have Administer database trigger system permissions.
In addition, when you delete a table or view, the triggers that are built on those objects are also deleted.

Status of the trigger
Status of the database trigger:
Active state (ENABLE): When the triggering event occurs, the database trigger trigger in the active state is triggered.
Invalid state (DISABLE): When the triggering event occurs, the database trigger TRIGGER in an invalid state will not be triggered, as is the case with this database trigger (TRIGGER).
These two states of the database trigger can be converted to each other. The format is:
ALTER Tigger Trigger_name [DISABLE | ENABLE];
Example: ALTER TRIGGER emp_view_delete DISABLE;
The ALTER TRIGGER statement can only change the state of one trigger at a time, while the ALTER TABLE statement can change the usage state of all the triggers associated with the specified table at one time. The format is:
ALTER TABLE [schema.] table_name {enable| DISABLE} all TRIGGERS;
Example: Invalidates all trigger on the table EMP:
ALTER TABLE emp DISABLE all TRIGGERS;

Pl-sql Trigger