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.
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.
Function:
1. Allow/restrict table modifications
2. automatically generate a derived column, such as an auto-increment Field
3. Forced Data Consistency
4. provide audit and logging
5. Prevent invalid transaction processing
6. Enable complex business logic
Start
Create trigger biufer_employees_department_id
Before insert or update
Of department_id
On employees
Referencing old as old_value
New as new_value
For each row
When (new_value.department_id <> 80)
Begin
: New_value.commission_pct: = 0;
End;
/
Trigger components:
1. Trigger name
2. Trigger statement
3. Trigger restrictions
4. trigger the operation
1. Trigger name
Create trigger biufer_employees_department_id
Naming Conventions:
Biufer (before insert update for each row)
Employees table name
Department_id column name
2. Trigger statement
For example:
DML statements on tables or views
DDL statement
Database shutdown or startup, startup shutdown, etc.
Before insert or update
Of department_id
On employees
Referencing old as old_value
New as new_value
For each row
Note:
1. Whether department_id is specified or not
2. When updating the department_id column of the employees table
3. Trigger restrictions
When (new_value.department_id <> 80)
Restrictions are not mandatory. In this example, if the column department_id is not equal to 80, the trigger will execute.
New_value indicates the new value.
4. trigger the operation
Is the subject of the trigger
Begin
: New_value.commission_pct: = 0;
End;
The subject is simple, that is, the updated commission_pct column is set to 0.
Trigger:
Insert into employees (employee_id,
Last_name, first_name, hire_date, job_id, email, department_id, salary, commission_pct)
Values (12345, 'chen', 'donny ', sysdate, 12, 'donny @ hotmail.com',. 25 );
Select commission_pct from employees where employee_id = 12345;
The trigger does not notify the user, so the user's input value is changed.
Trigger type:
1. Statement trigger
2. Row triggers
3. instead of trigger
4. system condition trigger
5. User event triggers
1. Statement trigger
It is a trigger on a specific statement or statement group executed on a table or view in some situations. With INSERT, UPDATE,
DELETE or join in combination. However, no matter what combination is used, each statement trigger will only activate the specified statement once.
. For example, no matter how many rows are updated, only one update statement trigger is called.
Example:
Users who need to perform DML operations on the table should be checked for proper privileges.
Create table foo (a number );
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in ('donny ') then
Raise_application_error (-20001, 'You don't have access to modify this table .');
End if;
End;
/
Even SYS and SYSTEM users cannot modify the foo table.
[Test]
Logs the time and characters of the table to be modified.
1. Create a test table
Create table employees_copy as select * from hr. employees
2. Create a log table
Create table employees_log (
Who varchar2 (30 ),
When date );
3. Create a statement trigger on the employees_copy table and fill in the employees_log table in the trigger.
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log (
Who, when)
Values (user, sysdate );
End;
/
4. Test
Update employees_copy set salary = salary * 1.1;
Select * from employess_log;
5. Are you sure which statement works?
Which of the INSERT/UPDATE/DELETE statements triggers the trigger?
You can use the INSERTING/UPDATING/DELETING condition predicate in the trigger for judgment:
Begin
If inserting then
-----
Elsif updating then
-----
Elsif deleting then
------
End if;
End;
If updating ('col1') or updating ('col2') then
------
End if;
[Test]
1. Modify the log table
Alter table employees_log
Add (action varchar2 (20 ));
2. Modify the trigger to record the statement type.
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
Rochelle action employees_log.action % type;
Begin
If inserting then
Rochelle action: = 'insert ';
Elsif updating then
Rochelle action: = 'update ';
Elsif deleting then
Rochelle action: = 'delete ';
Else
Raise_application_error (-20001, 'you should never ever get this error .');
Insert into employees_log (
Who, action, when)
Values (user, l_action, sysdate );
End;
/
3. Test
Insert into employees_copy (employee_id, last_name, email, hire_date, job_id)
Values (12345, 'chen', 'donny @ hotmail', sysdate, 12 );
Select * from employees_log
Update employees_copy set salary = 50000 where Maid = 12345;
2. Row triggers
It refers to the trigger activated for each affected row. It is defined similar to the statement trigger and has the following two exceptions:
1. The definition statement contains the for each row clause.
2. In BEFORE ...... In the for each row trigger, you can reference the affected ROW values.
For example:
Definition:
Create trigger biufer_employees_department_id
Before insert or update
Of department_id
On employees_copy
Referencing old as old_value
New as new_value
For each row
When (new_value.department_id <> 80)
Begin
: New_value.commission_pct: = 0;
End;
/
Referencing clause:
The default name of the value before running the DML statement is old, and the value is new.
Insert operation only: new
Delete operations only include: old
Update operations both have
The referencing clause only renames new and old to new_value and old_value to avoid confusion. For example
New table.
Not very useful.
[Test]: auto-incrementing serial number of the master node
Drop table foo;
Create table foo (id number, data varchar2 (20 ));
Create sequence foo_seq;
Create or replace trigger bifer_foo_id_pk
Before insert on foo
For each row
Begin
Select foo_seq.nextval into: new. id from dual;
End;
/
Insert into foo (data) values ('donny ');
Insert into foo values (5, 'chen ');
Select * from foo;
3. instead of trigger update View
Create or replace view company_phone_book
Select first_name | ',' | last_name name, email, phone_number,
Employee_id emp_id
From hr. employees;
Update email and name
Update hr. company_phone_book
Set name = 'chen1, donny1'
Where emp_id = 100.
Create or replace trigger update_name_company_phone_book
INSTEAD
Update on hr. company_phone_book
Begin
Update hr. employees
Set employee_id =: new. emp_id,
First_name = substr (: new. name, instr (: new. name, ',') + 2 ),
Last_name = substr (: new. name, 1, instr (: new. name, ',')-1 ),
Phone_number =: new. phone_number,
Email =: new. email
Where employee_id =: old. emp_id;
End;
4. system event triggers
System Events: Database startup, shutdown, server errors
Create trigger ad_startup
After startup
On database
Begin
-- Do some stuff
End;
/
5. User event triggers
User events: User logon and logout, CREATE/ALTER/DROP/ANALYZE/AUDIT/GRANT/REVOKE/
RENAME/TRUNCATE/LOGOFF
Example: delete an object
1. log table
Create table droped_objects (
Object_name varchar2 (30 ),
Object_type varchar2 (30 ),
Dropped_on date );
2. triggers
Create or replace trigger log_drop_trigger
Before drop on donny. schema
Begin
Insert into droped_objects values (
Ora_dict_obj_name, -- functions related to the trigger
Ora_dict_obj_type,
Sysdate );
End;
/
3. Test
Create table drop_me (a number );
Create view drop_me_view as select * from drop_me;
Drop view drop_me_view;
Drop table drop_me;
Select * from droped_objects
Disable and enable triggers
Alter trigger Disable;
Alter trigger Enable;
Transaction processing:
Commit/rollback cannot be used in triggers
Because ddl statements have implicit commit
View:
Dba_triggers
