Application of Oracle triggers in MIS Development

Application of Oracle triggers in MIS development this article introduces the concepts and types of oracle triggers, summarizes the application of oracle triggers in MIS development, and provides examples for reference. 1. introduction www.2cto.com when I am working on a large MIS system (the front-end uses the powerbuild tool and the back-end uses the oracle database), I often encounter problems related to data lengthy, batch deletion, and dynamic information, it is found that database Triggers are easy to use. I will summarize some experiences in development and application for your reference. 2. Concepts and types of triggers. Database triggers are executed in response to database events such as insertion, update, or deletion. It defines the actions that should be taken when database-related events occur. It can be used to manage complex integrity constraints, monitor table modifications, or notify other programs that the table has been modified. Its types include statement-level triggers and row-level triggers. The former can be triggered before or after a statement is executed. The latter is triggered once on the line affected by each trigger statement. There are also commands triggered by before and after. Execute before or after insert, update, and delete, and reference new and old values for processing. If you need to use a trigger to set a column value in the inserted row, you need to use a trigger before insert to access the "new" value, but not after insert. The Instead of trigger command tells oracle what operations should be performed. The above four categories combine 14 small categories. The execution sequence of various triggers is as follows: if www.2cto.com exists, the statement-level before trigger is first executed. Each row affected by insert, delete, and update. If yes, the first row-level before executes the delete or update operations. If yes, the row-level after trigger is executed. If yes, the statement-level after trigger is executed. 3. database Triggers are used to manage data redundancy. To meet the needs of data analysis and report generation, you must use triggers to add redundant data to the data model to ensure data integrity. Redundant data can be managed using the before update trigger of the specified for each row option. The update command can be used in a trigger to update all the redundant data to be updated, such as the customer table and order table. The order table includes the customer's order and customer table redundancy information) table Structure: Customer ID (cu_no), customer name (cu_name), and customer address (cu_address ). Order table structure: order Number (or_no), customer number (or_no), customer name (cu_name), customer address (cu_address ), when the data in the customer base table is updated, the redundant columns in the order are updated. Syntax: 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. if you need to use a trigger to complete data replication, you can use a data trigger to copy data from one database to another. If the data replication requirement is only related to data insertion, when a record is inserted into a base table in a database, you also want to insert this record into a remote database. You need to create a connection to the remote database using the create database link statement, once a database connection is created, you can create an after insert trigger on the base table to insert each record. To the remote database. (1) Create a database link bj_ysd_remote base table in the script as the base table of the database. Bj_ysd_local indicates the source base table of 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 indicates the field name */end; (3) delete the record create or replace trigger trig_ysd_del after delete on bj_ysd_local for each row begin delete from bj_ysd_remote @ dblink remote where x1 =: old. x1 end; 5. when you use a database trigger to delete a record in a bucket, the record is a record related to a foreign key, this delete operation must be passed in the model. Otherwise, a large amount of lengthy data may occur. The example uses the cumstomer and order base tables as examples. When a customer is deleted from the customer, all related records in the order base table should also be deleted. Create or replace trigger trig_cust Before delete on customer For each row Begin Delete from order; Where cu_no = old. cu_no; End; 6. when you use a trigger to perform dynamic data operations, you can use a trigger to solve the problem of how to implement dynamic inventory. In order to complete Dynamic Inventory operations, data such as acceptance, warehouse picking, stock transfer, decommission, stock return, and sales licensing must be added or subtracted from the previous inventory. This article only uses the acceptance trigger as an example, and the other structures are similar. They involve two base tables: bi_ysd (acceptance form), Bj_kcb (current database table), the former table structure (rq (date), ysdh (acceptance form number), bjbm (spare part code ), yssl (Acceptance quantity), ysdj (Acceptance unit price), the latter table structure is (bjbm (spare part code), dqkcl (current inventory), dqkcje (current inventory amount )) the trigger is as follows: create or replace trigger trig_ysd after insert or update or delete on bj_ysd for each row declare rq1 varchar2 (8); rq2 varchar2 (8);/* limited space, yssl1, yssl2, ysdj1, ysdj2, bjbm1, bjbm2, and ii statements */if inserting or updating then rq1: =: new. rq; bjbm1: =: new. bjbm; yssl1: =: New. yssl; 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 deleting or updating then rq2: =: old. rq; bjbm2: =: old. bjbm; yssl2: =: old. yssl; ysdj2: =: old. ysdj; update bj_dqkcb set dqkcb = dqkcl-yssl2; dqkcje = dqkcje-yssl 2 * ysdj2 end if; end; 7. conclusion: database Triggers are widely used in database development and MIS development, but experience shows that using too many triggers will reduce the performance of the entire database. If the database trigger is poorly written, it will quickly damage the database performance. Therefore, it is very important to use the appropriate trigger when appropriate.