pl/sql触发器_PL / SQL触发器

pl/sql触发器

PL/SQL triggers are block structures or pre-defined programs, which may be in-built or even explicitly developed by the programmers for a particular task. Triggers are automatically executed when an event occurs which is well thought of by the programmer in advance.

Triggers are associated with response-based events such as a Database Definition Language (DDL) statement such as CREATE, DROP or ALTER or a Database Manipulation Language (DML) statement such as UPDATE, INSERT or DELETE or any other database operation such as a Startup, Shutdown, Logging in and Logging Out.

A trigger therefore helps to prevent invalid transactions, duplication of tables/data, forced referential integrity and provides security authorizations.

The parts of a PL/SQL trigger are SQL statements of a trigger, trigger action statement and trigger restrictions.

Creating PL/SQL Triggers

Syntax

Create or Replace Trigger Trigger_Name: This statement creates a trigger with the given name or overwrites an existing trigger with the same name.

Before or After or Instead of: This statement specifies the time at which the trigger should be executed i.e., either before or after updating/Inserting/deleting the values in a table. 

Insert or Update or Delete: This specifies the DML operation. This statement determines the triggering event. One or more triggering event can be used together if needed. The trigger gets fired at all the specified triggering event. 

Of  Column_Name: This statement is only used with triggers who have Update event and when a specific column is updated.

On Table_Name: This statement specifies the name of the view or table with which the particular trigger has to be associated.

[Referencing OLD AS O New AS N]: This statement allows us to refer Values (Old and New) for Data Manipulation language (DML) statements such as Delete, Insert or Update. This statement is optional and is useful for referring the new and old values of the data that needs to be changed. It is not possible to refer an old value when inserting a new record or to refer to new value when deleting an old record

For Each Row: This statement is used to specify whether the trigger being developed is a row level trigger (each row gets influenced) or a statement level trigger or a table level trigger (executes just once when the PL/SQL statement is executed). 

When (Condition): This statement is valid only for a row level trigger. This helps the trigger to be executed only when the specified condition evaluated to be TRUE. 

Types of Triggers in PL/SQL

There are two types of basic triggers in PL/SQL. There can be a number of combinations of triggers as and when needed.

Row level trigger: Here, the event is executed for each row which may insert, delete or update a data.

Statement level trigger: Here, the event is executed foreach SQL statement or a complete table.

PL/SQL Triggers Example

Here, we have created a trigger which ensures that we don’t enter any Admin who has a salary less than 1000.

create or replace trigger UpdateSalary
before insert or update on Admin
for each row
when(new.asal<1000)
begin
raise_application_error(-20189,'Salary Must be Greater than 1000');
end;
/

Output

To check whether this trigger works perfectly or not, we try to insert new value into the database.

insert into Admin values(5, 'Vedant Mishra', 500);

Output

PL/SQL Trigger Commands

Enable a Trigger

If a trigger is disabled, you can alter/change it to enable it to perform the desired actions. 

Syntax

Alter Trigger Trigger_Name Enable;

Drop a Trigger

If  you want to delete a trigger from the system, you can use the drop command.

Syntax

Disable a Trigger

If you don’t want a trigger to execute itself for a while without deleting it from the system or database, you can alter/change its value by disabling it.

Syntax

Alter Trigger Trigger_Name Disable;

翻译自: https://www.thecrazyprogrammer.com/2015/07/plsql-triggers.html

pl/sql触发器