Application of Oracle trigger in MIS development
Author: Zhou Haitao Wu Lianggang
Abstract: This article describes the concepts and types of Oracle triggers, summarizes the application of Oracle trigger in the development of MIS and provides an example for reference.
Keywords: MIS, trigger Oracle
1. introduction
I am doing a large MIS system (front desk with PowerBuild tools, with Oracle Database in the background), some issues related to data length, bulk deletion, and dynamic information are often discovered, thereby discovering that the database trigger is easy to use. Some experiences in development and application are now summarized for peer references.
2. Trigger concept and type.
Database triggers are processed in response to database events such as insertion, update, or delete. It defines the actions that should be taken when some database related events occur. Can be used to manage complex integrity constraints or monitor the modifications of the table, or inform other programs, the table has been modified. Its types are: statement level trigger, and row-level trigger, the former can trigger before or after execution of statements. The latter triggers a row that affects each trigger statement. There is also a command triggered by Before and After. Insert, Update, and DELETE, reference to the new old value for processing. If you need to set a column inserted in the row through the trigger, you need to use a trigger Before INSERT to use AFTER INSERT. The INSTEAD OF trip command uses it to tell Oracle what to do. 14 small classes (omitted) were synthesized above. The order of execution of various triggers is as follows:
(1) If there is, the statement-level Before trigger is first executed.
(2) Each INSERT, DELETE, UPDATE is affected;
1 If there is, the first executive grade before
2 DELETE or UPDATE executive
3 If there is, execute a row AFTER trigger
(3) If there is, execute the statement level AFTER trigger
3. Data redundancy using database triggers
For data analysis and production of reports, users add redundant data in the data model, and the trigger should be used to ensure the integrity of the data.
Redundant data can be managed with the predetermined For Each ROW option. The UPDATE command can be placed in the trigger to update all redundant data that require updates, such as customer tables and order tables, order tables including customer orders and redundant information for customer tables, customer table structure: Customer No. (CU_NO), customer name (CU_NAME), customer address (CU_ADDRESS). Order table structure: Order number (OR_NO), customer number (OR_NO), customer name (CU_NAME), customer address (CU_ADDRESS), updating redundant columns in the order when the data in the customer base table is updated . The syntax is as follows:
Create or Replace Trigger BJ_CUSTOMER
Before Update on Customer
For Each Row
Begin
Update Order Set
Cu_no =: new.cu_no,
Cu_name =: new.cu_name,
Cu_address =: new.cu_addess,
WHERE CU_NO =: Old.cu_no;
END;
4. Complete data replication with triggers
If the demand is very limited, you can copy data from one database from one database. If the data replication requirements are only related to the insertion of the data, when a record is inserted into a foundation table in a database, the user is still I hope to insert this record into a remote database. You need to create a connection to the remote database with the CREATE DATABASE LINK statement. Once you have created a database connection, you can create an After INSERT trigger on the base table to put each A record is inserted into the remote database. (1) Creating a database connection in the script (Database Link) BJ_YSD_REMOTE Base Table as a database base table, BJ_YSD_LOCAL represents the source foundation table on the local database.
Creat Database Link Remote (Connection Name)
Connect To BJ (Account) Indentified by BJ (Password)
Using ': 2';
(2) Copy record
Create or Replace Trigger Trig_YSD (Trigger Name)
After INSERT ON BJ_YSD_LOCAL
For Each Row
Begin
INSERT INTO BJ_YSD_REMOTE @ DBLINK REMOTE
Value (: new.x1,: new.x2, ...) / * x1.x2 representative field name * /
END;
(3) Delete record
Create Or Replace Trigger Trig_YSD_DEL
After delete on bj_ysd_local
For Each Row
Begin
Delete from bj_ysd_remote @ dblink transote
Where x1 =: Old.x1
END;
5. Follow the database trigger to complete the waterfall delete operation
In some cases, when a recording is to be deleted, the record is a record on another base table associated with the foreign key, and this deletion must be passed in the model, otherwise a large amount of lengthy data will occur, still in cumstomer. And the Order Base Table as an example, when a customer is removed from the Customer, all related records in the Order base table should also be deleted.
Create or Replace Trigger Trig_cust
Before delete on custom
For Each Row
Begin
Delete from Order
WHERE CU_NO = Old.cu_no;
END;
6. Use the trigger to complete the operation of dynamic data
The trigger can be solved with a trigger that involves how to achieve dynamic stocks. The warehouse has an acceptance, outbound, allocated, scrapped, retreat, and the sale of these data must be added to the previous inventory to complete the dynamic inventory operation. This article is only used as an example of an acceptance single trigger, and other structures are similar. They involve two base tables: BI_YSD (acceptance), BJ_KCB (current inventory table), the former's table structure (RQ (date), YSDH (acceptance number), BJBM (spare parts encoding), YSSL (number of acceptance), YSDJ (acceptance unit price)), the latter's table structure is (BJBM (spare parts encoding), DQKCL (current inventory), DQKCJE (current stock amount)) trigger is as follows:
Create Or Replace Trigger Trig_YSD
After insert or update or delete on bj_ysdfor each row
Declare rq1 varchar2 (8); RQ2 varchar2 (8);
/ * Is limited to space, YSL1, YSSL2, YSDJ1, YSDJ2, BJBM1, BJBM2, II declarations * /
IF inserting or updating the
RQ1: =: new.rq; bjbm1: =: new.bjbm; yssl1: =: new.ysl;
Ysdj1: =: new.ysdj;
SELECT Count (*) INTO II from BJ_DQKCB
WHERE BJBM = BJBM1;
IF II = 0 THEN
INSERT INTO BJ_DQKCB (BJBM, DQKCL, DQKCJE)
Value (BJBM1, YSSL1, YSDJ1);
Else
Update bj_dqkcb
SET DQKCL = DQKCL YSSL1;
DQKCJE = DQKCJE YSSL1 * YSDJ1;
END IF
END IF
IF deletring or updating the
RQ2: =: Old.rq; bjbm2: =: Old.bjbm; yssl2: =: ild.ysl;
YSDJ2: =: Old.ysdj;
Update bj_dqkcb
SET DQKCB = DQKCL-YSL2;
DQKCJE = DQKCJE-YSL2 * YSDJ2
END IF;
END;
7. Conclude
Database triggers have a wide range of applications in database development, MIS development, but experience shows that excessive triggers will reduce the performance of the entire database. If the database trigger is not written, it will quickly destroy the performance of the database, so it is very important to use the appropriate trigger when appropriate.
