How to use oracle triggers

A trigger is a code block automatically executed when a specific event occurs. It is similar to a stored procedure, but users cannot directly call them. Triggers are a technology provided by many relational database systems. In the ORACLE system, triggers and similar procedures and functions have PL/SQL blocks that declare, execute, and handle exceptions.

1. Description

1) A trigger is a special stored procedure. A trigger is generally triggered by an event and cannot accept parameters. The memory is called by the statement block.

2) trigger category:

1. DML trigger: created on a table and triggered by a DML event

2. instead of trigger: created on the view and can only be triggered at the row level, used to replace insert, delete and other operations (because oracle cannot directly perform DML operations on views created from more than two tables, an alternative trigger is provided, which is a special way to handle view operations)

3. DDL trigger: Creates and modifies database objects when an event is triggered.

4. Database event triggers: are defined in the database or mode and triggered by database events.

3) composition:

1. trigger event: the DML statement that triggers the trigger (the INSERT, UPDATE, and DELETE statements perform data processing on the table or view) DDL statements (such as CREATE, ALTER, and DROP statements) and database system events

(Such as system startup or exit, abnormal errors), user events (such as logon or database exit ).

2. trigger time: whether the trigger is triggered BEFORE or AFTER the trigger event.

3. Trigger operation: the tasks to be completed after the trigger is triggered.

4. Trigger objects: including tables, views, modes, and databases. Only when trigger events that meet the trigger conditions occur on these objects can the trigger be triggered.

5. Trigger condition: A logical expression is specified by the WHEN clause. The trigger operation is automatically executed only when the value of this expression is TRUE.

6. Trigger frequency: the number of times the action defined in the trigger is executed. STATEMENT-level (STATEMENT) triggers and ROW-level (ROW) triggers. (For example, when multiple data records are deleted, the row-Level Trigger may be executed multiple times, and the statement-level trigger will be triggered only once)

2. Syntax

1) Description

Different types of triggers, such as DML triggers and Instead of triggers, have different syntax formats.

2) General syntax

CREATE [or replace] TIGGER trigger name trigger time trigger event

ON Table Name/view name

[For each row] // Add for each row as a ROW-Level Trigger. If no ROW is added, it is a statement-Level Trigger BEGIN pl/SQL statement.

END

Create [or replace] trigger [schema.] trigger_name

{Before | after | instead}

{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> <insert | update | delete> ON table_name

[For each row] WHEN (condition)

Declare begin end;

3) instead of trigger syntax

Syntax:

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}] // you can specify relevant names. The current default names are OLD and NEW,

The Application name must be added: [for each row] // instead of trigger can only be triggered at the ROW level, because it is not necessary to specify [when condition] DECLARE

BEGINEND;

Note: instead of is used to trigger dml of a view. Because the view may have multiple tables joined, not all joins can be updated. You can use the instead of trigger to complete the corresponding operations.

3. Instance

Create a test table:

Create table "HNZC". "TRIGGERTEST"

(

"ID" VARCHAR2 (20 BYTE ),

"NAME" VARCHAR2 (20 BYTE ),

"SCORE" NUMBER

); Create table tab1 select * from triggertest;

1) DML triggers/row-level triggers

The trigger is as follows:

Create or replace trigger TRIGGER1

After insert on triggertest // triggers for each row after insert // ROW-Level Trigger BEGIN

Insert into tab1 (ID, NAME) VALUES ('22', '33'); END;

Execute the statement:

Insert into triggertest (id) values ('abbcc ');

When the statement execution ends, a new piece of data is added to table tab1.

2) restrict table modification (for example, some tables cannot be modified during non-work hours)

The trigger is as follows:

Create or replace trigger TRIGGER1

AFTER INSERT ON TRIGGERTEST

FOR EACH ROW

BEGIN

IF (TO_CHAR (SYSDATE, 'day') IN ('wedday', 'sunday') THEN RAISE_APPLICATION_ERROR (-20001, 'is not the start time, the table triggertest' cannot be modified '); end if; END;

Execute the statement:

Insert into triggertest (id) values ('abbcc ');

The output result for Wednesday is as follows:

An error occurred while executing the command on Row 1: insert into triggertest (id) values ('aabbcc ')

Error Report:

SQL error: ORA-20001: Not working time, cannot modify table triggertest

ORA-06512: error during execution of "HNZC. TRIGGER1", line 3ORA-04088: trigger 'hnzc. trigger1'

The following table restrictions are generally imposed (that is, the table can be modified from on Monday to on Friday)

Create or replace trigger TRIGGER1

BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

FOR EACH ROW

BEGIN

IF (TO_CHAR (SYSDATE, 'day') IN ('satur', 'sunday') OR (TO_CHAR (SYSDATE, 'hh24: MI ') not between '9: 00 'AND '18: 00') THEN RAISE_APPLICATION_ERROR (-20001,' the table triggertest cannot be modified because it is not the start time. '); end if; END;

3) add restrictions (for example, you cannot change the records of an employee)

The trigger is as follows)

Create or replace trigger TRIGGER1

BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

FOR EACH ROW

WHEN (OLD. NAME = 'month') BEGIN

Case when updating ('score ') THEN

IF: NEW. SCORE <: OLD. score then RAISE_APPLICATION_ERROR (-20001, 'scores of months can only be increased but cannot be downgraded '); end if; end case; END;

The score for the current month is 20.

An error occurred when changed to 10.

UPDATE "HNZC". "TRIGGERTEST" set score = '10' where rowid = 'aaadezaapaaaah + aab' AND ORA_ROWSCN = '000000' ORA-47685303: The SCORE of a month can only be increased AND cannot be decreased.

ORA-06512: error during execution of "HNZC. TRIGGER1", line 4ORA-04088: trigger 'hnzc. trigger1'

Successful When changed to 30

UPDATE "HNZC". "TRIGGERTEST" set score = '30' where rowid = 'aaadezaapaaaah + aab' AND ORA_ROWSCN = '2016' submitted successfully

4) Call the stored procedure in the trigger

Trigger:

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 replacePROCEDURE TESTPRO1 AS BEGIN

Insert into tab1 (id, name, score) VALUES ('aaa', 'bbb ', 200); END TESTPRO1;

Add a data entry to tab1 after execution.

5) cascade update

The trigger is as follows (the name in tab1 is also modified when the name in the triggertest table is modified)

Create or replacePROCEDURE TESTPRO1 AS BEGIN

Insert into tab1 (id, name, score) VALUES ('aaa', 'bbb ', 200); END TESTPRO1;

Execute the statement:

Update triggertest set name = 'waterer' where name = 'month ';

Result: In tab1, if the name is month, it is also changed to water.

6) instead of trigger

Table student table data is as follows:

Create view student_view

Create or replace view STUDNET_VIEW

As select classid, AVG (SCORE) AVERAGE_SCORE from studentgroup by classid;

View data is as follows:

Execute the following operations on the view student_view:

Delete from STUDNET_VIEW where classid = '000000 ';

Execution result:

Error Report:

SQL error: ORA-01732: The data manipulation operation for this view is invalid 01732. 00000-"data manipulation operation not legal on this view"

Solution: Create an instead of View

Create or replace trigger STUDENT_VIEW_DELETE

Instead of delete on STUDNET_VIEW

FOR EACH ROWBEGIN

Delete from student where classid =: OLD. CLASSID; END STUDENT_VIEW_DELETE;

Execute the delete statement.

Delete from STUDNET_VIEW where classid = '000000 ';

Execution result: Deletion successful

One row has been deleted.

4. Notes

1) only DML statements (SELECT, INSERT, UPDATE, and DELETE) can be used for trigger execution, and DDL statements (CREATE, ALTER, and DROP) cannot be used)

2) The commit statement cannot be used in the trigger. The trigger operation is performed together with the trigger event (INSERT, UPDATE, DELETE) for COMMIT and ROLLBACK;

3) The more triggers on a table, the greater the impact on the table's DML operation performance.

4) trigger up to 32 KB