Concepts and writing of Oracle triggers, and writing of oracle triggers

Concepts and writing of Oracle triggers, and writing of oracle triggers
1.Trigger concept:

A trigger is a program unit stored on the server. When some events in the database occur (such as insert \ update \ delete), the database automatically starts the trigger and executes the corresponding operations in the trigger.

(1) A trigger is a special stored procedure with the transaction function;

(2) A trigger cannot be called directly, but is triggered by an event. For example, add, delete, modify, or delete a trigger.

(3) triggers are often used to enhance data integrity constraints and business rules.

To put it simply, the trigger is a switch, and the light is the operation after the switch is triggered. When the switch is triggered, the light is on.

2. Trigger event:

Insert, update, delete, create (when an object is created), alter, drop

Logon/logoff)

Startup/shutdown (trigger executed when the database is opened or closed ).

3. Trigger time

Before executes the trigger before the specified event occurs.

After, the trigger is executed after the specified event occurs.

4. Trigger level 4.1 line-level triggering

Row-based trigger: Execute the trigger on each row that affects the trigger event, that is, the trigger mechanism is based on rows. Change a row of data and trigger it once.

This type of trigger will be triggered after the insert \ update \ delete operation is complete, that is, after. (That is, after is used for addition, deletion, and modification)

Key statement: for each row

-- Case 2

Create or replace tirgger t_mydel

After delete

On stuinfo

For each row

Begin

Dbms_output.put_line ('deleted! ');

End;

-- Execute (delete a record, and the result shows a 'deleted good ')

Delete from stuinfo where stuname = 'Li wencai ';

-- Execute (records in the entire table are deleted, and N 'deleted good' entries are displayed ')

Delete from stuinfo

-- Note: This explains the row-level triggers.

-- Row-Level Trigger: deletes a row. Deleting a row triggers a row-Level Trigger.

-- Statement-Level Trigger: execute a trigger once.

4.2 statement triggering

Statement trigger: the trigger event can only be triggered once, and the value of each row affected by the trigger cannot be queried. The trigger is triggered only once no matter how many records this SQL statement affects.

5. Create a trigger

Create [or replace] trigger trigger_name

After | before | instead of -- instead reverse

[Insert] [[or] update [of column list] [[or] delete]

On table or view

[For each row] -- row-level Mode

Begin

-- Pl/SQL statement (begin... end)

End;

Case 1:

-- Create a trigger tig_1, Which is prompted when you delete data in the scott. emp table.

SQL> create or replace trigger tig_1

After delete

On scott. emp // Note: for each row is not used here, that is, the trigger is triggered only once no matter how many items are deleted.

Begin

If deleting then

Dbms_output.put_line ('the user deleted the data in the emp table! ');

End if;

End;

Case 2:

-- Create a trigger tig_2. When the value of the deptno column in the table scott. dept changes, the value of the deptno column in the table scott. emp is automatically updated to ensure data integrity.

SQL> create or replace trigger tig_2

After update

On scott. emp

For each row

Begin

Update scott. emp set deptno =: new. deptno

Where deptno =: old. deptno;

End;

Note:

This program has two concepts: new and: old,: new represents the new table after the update operation is executed, and: old represents the old table before the update operation is performed.

With the use of these two tables, you can access the changes in table data before and after the trigger is executed.

Insert operations only include: new, delete operations only include: old, and update operations.

: New and: old are only used for Row-level triggers.

--: New table. Insert the inserted data to the new table first, confirm the data, and put it to the table to be updated.

--: Old table, which puts unwanted data into the old table first, and then clears the old table after confirmation.

-- Note: the new table and the old table have only one data entry from the beginning to the end. How many columns are there? The number of columns in the trigger table: new and old.

-- Case:

SQL> insert into emp values (..., James ,..., 0 ,......);

SQL> create or replace trigger trig_name

After insert -- after the insert operation

On emp -- In the emp table

For each row-level Mode

Begin

If (: new. sal <= 0) then -- new table: sal in new equals 0

Dbms_output.put_line ('Warning: you cannot afford to pay for your work ');

Rollback; (exception can be used: Roll Back data after throwing an exception)

Else

Dbms_output.put_line ('inserted record ');

Commit;

End if;

End;

6. Trigger composition:

A. Trigger Statement (event)-define DML events and DDL events for activating a trigger;

B. Trigger restrictions-Conditions for executing a trigger. Only when the trigger is true can the trigger be activated;

C. Trigger operations (subject)-including SQL statements and code. They run only when the trigger statement is issued and the trigger limit value is true.

Note: The sequence is saved to the database after it is triggered.

Case study: Create a trigger in a cascade table

-- Li siwen cannot delete

(When will it be triggered? A. delete; B. when the when condition is met; c. The Code specifies)

SQL> create or replace trigger t_studel

After delete -- after the delete operation

On stuinfo

For each row

Begin

If: old. stuname = 'liyun' then -- if the old table contains liyun.com, the system prompts that it cannot be deleted.

-- Throw an exception

Raise_application_error (-20010, 'this student cannot delete !!! ');

End if;

End;

SQL> delete from emp;

-- Li siwen cannot be deleted or changed

You only need to modify the following code in the previous example:

......

After delete or update

......

-- How do I know which operation is (insert \ delete \ update? It doesn't matter if you don't know. Use the case when statement.

......

Begin

Case

When deleting then

-- Delete

If: old. stuname = 'lisvin' then

Raise_application_error (-20010, 'this student cannot delete !!! ');

End if;

When updating then

-- Modify

If: old. stuname = 'lisvin' then

Raise_application_error (-20011, 'this student cannot modify !!! ');

End if;

When inserting then

-- Insert

If: new. stuname = 'zhang yang' then

Raise_application_error (-20012, 'you can recruit students !!! ');

End if;

End case;

End;

7. Multi-table join trigger

Write a delete trigger in the stuinfo table to specify the name of the deleted person,

Delete the score of the person in the stumarks table.

Create or replace tirgger t_mydel

After delete

On stuinfo

For each row

Declare

Stu varchar2 (22 );

Begin

Stu: =: old. stuno; -- assign the student ID to this variable.

Delete from stumarks where stuno = stu;

End;

-- Run

Delete from stuinfo where stuname = 'lisboa ';

8. Trigger application (ID column ):

ID column: sequence + trigger

-- Solution:

(1) create a table

Create table tb_715 (

Sid number,

Sname varchar2 (22)

)

(2) create a sequence

Create sequence seq715;

(3) create a trigger, which is triggered before use, and use the new table

Create or replace trigger t_insert

Before insert

On tb_715

For each row

Begin

-- Id = the next value of the sequence in the new table

: New. sid: = seq715.nextval;

End;

**************************************** *

Oracle 11g Syntax:

: New. sid: = seq715.nextval;

Oracle 10g Syntax:

Select seq715.nextval into: new. sid from dual;

Do not use variables. The method of using variables is the method of writing 11 GB.

Oracle trigger points: Pre-trigger and post-trigger