Oracle creation sequence and trigger Creation

Sequence: sequence creation method and use when data is inserted; -- sequence creation createsequencesqincrementby1startwith1maxvalue10minvalue

Sequence: sequence creation method and use when data is inserted; -- sequence creation create sequence sqincrement by 1 start with 1 maxvalue 10 minvalue

Sequence: sequence creation method and use when inserting data;

-- Sequence Creation
Create sequence sq
Increment by 1
Start with 1
Maxvalue 10
Minvalue 1
Cycle
Cache 5

-- General (a sequence can be used in multiple tables, but generally, a table corresponds to a sequence)
Create sequence sq
Increment by 1
Start with 1
Nocache
Nocycle

-- Sequence Method
Insert into emp (empno, ename)
Values (sq. nextval, 'Tim ');

-- View data
Select * from emp;

Trigger:

-- Trigger: Special stored procedure.
-- Feature: it cannot be called manually directly and can only be triggered automatically (triggered by one action ).
-- Type: dml trigger, instead alternative trigger, system trigger
-- Dml trigger
-- 1. Statement level (only one operation is triggered when an operation statement is executed)
-- Syntax:
Create or replace trigger tri_XXX
Action on table
Declare
......
Begin
......
End;

-- Example: Raise the collective salary of 200 yuan for employees in department 30 (trigger a statement-Level Trigger ).
-- Create a trigger (similar to creating a stored procedure before it is called)
Create or replace trigger tri_update_emp
After update on emp -- execute the trigger after the modification is completed
Begin
Dbms_output.put_line ('salary increase ......');
End;
-- Trigger
Update emp set sal = sal + 200 where deptno = 30;

-- Create a trigger (similar to creating a stored procedure before it is called)
Create or replace trigger tri_update_emp
Before update on emp -- triggered before modification
Begin
Dbms_output.put_line ('salary increase ......');
End;
-- Trigger
Update emp set sal = sal + 200 where deptno = 30;

-- 2. Row-level (trigger once if no row is modified)
Create or replace trigger tri_update_emp
After update on emp -- execute the trigger after the modification is completed
For each row
Begin
Dbms_output.put_line ('salary increase ......');
End;
-- Trigger
Update emp set sal = sal + 200 where deptno = 30;

-- Example
Create or replace trigger tri_up_del_ins_emp
After delete or insert or update on emp -- execute the trigger after the modification is completed.
For each row
Begin
Dbms_output.put_line ('triggered ......');
End;
-- Trigger
Delete from emp where deptno = 30;

-- Condition predicates (boolean type): inserting, updating, and deleting
Create or replace trigger tri_up_del_ins_emp
After delete or insert or update on emp -- execute the trigger after the modification is completed.
For each row
Begin
If inserting then
Dbms_output.put_line ('new guy, oo ......');
Elsif updating then
Dbms_output.put_line ('Modified, it does not work ......');
Else
Dbms_output.put_line ('fired ......');
End if;

End;
-- Trigger
Delete from emp where deptno = 30;

-- Example: simple Library Management System
Select * from book;
Select * from borrow;
-- Add a column
Alter table book
Add countOfBook integer check (countOfBook> = 0 );
-- To complete the book borrowing function, insert a row into the borrow table. The book table corresponds to the book inventory-1.
-- Question 1: on which table is the trigger created? Borrow
-- Question 2: How to pass the data inserted into the borrow table to the book? : NEW
-- ***** A row-Level Trigger with two special Variables
--: New -- automatically stores newly inserted data records (one row of data) and modified record rows
--: Old -- automatically stores deleted data records (one row of data), and modifies the previous record rows.
-- Example
Create or replace trigger tr_up_emp
After update on emp
For each row
Begin
Dbms_output.put_line (: old. ename |: old. sal );
Dbms_output.put_line (: new. ename |: new. sal );
End;
-- Trigger
Update emp set ename = 'ao-smith ', sal = 250
Where ename = 'Smith ';

SELECT * FROM book;
-- Implement the function of borrowing books
-- Step 1: Create a trigger on borrow to automatically modify the book table.
Create or replace trigger tri_in_borrow
After insert on borrow
For each row
Begin
Update book set countOfBook = countOfBook-1
Where bid =: new. bid;
End;
-- Step 2: insert data in borrow.
Insert into borrow
Values ('t013 ', '123', 'b003', sysdate, null );

-- Job: P322 11 and 12. The borrow function in the above example

For more details, please continue to read the highlights on the next page:

Related reading:

Use of Oracle triggers

An ORA-04091 exception occurred when the Oracle trigger assigned a value to the table's own field.

Create a trigger in Oracle to call stored procedures with Parameters

Oracle trigger query statistics table