Application of Oracle Trigger in the development of log management

Absrtact: This paper discusses the method of designing and implementing the log management by using the trigger in the database, and it is a kind of writing the log management method in the client software.
Improvements, and gives an example demonstration in Oracle9i.
Key words: Oracle; Trigger Log Management
Middle Image classification Number: TP311
Document Identification code: A
Article ID: 1009-3044 (2008) 16-21186-02
The application of Oracle Trigger in the developing of Log Management
WU Heng-liang, ZHANG Wei-wei
(Shandong Business Colloge,management Science and Engineering Institute,yantai 264005,china)
Abstract:this article describes a new developing method of log management by the trigger of data base, which is an improvem ENT to the O
Riginal method of writing log management in the client software, and the article gives the example of application in Oracl E9i.
Key words:oracle; Trigger Log Management
1 Introduction
Logs are very important for security, and it records a variety of things that happen every day in the system. The main functions of the log are: auditing and monitoring. On the one hand can be through it
To check the cause of the error, or the traces left by the attacker when the attack occurred. On the other hand, it can monitor the system status, monitor and trace the intruder and so on.
In every enterprise management Information system software development process, generally involved in the Log management module, because each software has a variety of operating rights of operators
Engaged in the operation within its purview, we must record the full history of these operations, to monitor the operation of the system, for the post-mortem supervision to provide a direct basis for the prevention department
important role in security.
In the development of C/S/b mode system software, the general method of log management is on the client (c/s) or application server (b/s) on each function module to write the log
Processing sub-modules, this method of programming is relatively large, development efficiency is low, maintenance is more cumbersome. This article attempts to use trigger programming in the database to manage all or
Section goes to the database, comparing the previous method can greatly reduce the programming code, reduce the workload of development.
2 Introduction to Triggers
modifying, deleting, or inserting data in a table is a very common operation and should be automatically signaled to other programs that need to perform operations when the table is modified. Triggers can be
To accomplish this, a trigger is a stored procedure that is stored in a database that is associated with a data table and is executed automatically when an INSERT, update, or delete operation occurs.
Unlike general stored procedure invocation methods, when an event occurs that causes a serial update or other corresponding action, it is implemented by automating the trigger code instead of manually
Intervention, greatly reducing the maintenance work, but also good to ensure the consistency of the data.
The advantage of triggers is that they are automatically fired, regardless of what caused the data to be modified (from the program or from the user), and they all work, so they are often used in different data tables
The related data of the serial connection modification. This method is used to realize indirect data association of data tables, which can be controlled by the centralized database, and only modify the corresponding triggering when the rules change.
System is easy to maintain and improves efficiency.
The basic idea of this paper is to make use of this point, the client software automatically triggers the trigger when each of these actions is issued to the table, and realizes the log record. In various
This kind of program is provided basically in RDBMS, this article introduces how to use the trigger using oracle9i as an example.
In Oracle9i, a trigger is a programming structure of PL/SQL. The following is a quick introduction to how triggers are used.
2.1 Main syntax for building triggers
create [or replace] trigger Trigger_name {before|after}event
On {table | View |database}
[For each row[when condition]]
Trigger_body
Comments:
Trigger_name: The name of the trigger;
Event: For INSERT, UPDATE, delete, or update of column name 1, column name 2?;
On {table | view |database}: Trigger object for trigger (table, view, or database);
[For each row]: optional, when using this option, each corresponding line will cause trigger trigger;
Trigger_body: the PL/SQL block executed when the trigger is triggered.
2.2 Main applications of triggers
Received: 2008-04-17
Author Profile: Wu Hengliang (1978-), Male, Shandong Laiwu, Shandong Business School lecturer, master, research direction for management information systems, e-commerce, data warehousing and data mining
such as Zhang Weiwei (1978-), Female (Manchu), Liaoning Dandong people, Shandong Business School lecturer, Master's degree, Research direction for project management, data warehousing and data mining.
1186
Database and information management
(1) Implementation of the integrity of the table constraints, such as setting the data table constraints, referential integrity and so on. Especially for those that cannot be implemented through declaration restrictions during the table creation phase.
Complex security, integrity restrictions, such as restricting the user's access time, and so on;
(2) Implementation of the table operation of the pre-processing and post-processing, such as the contents of the table changes, to other needs to run the program signal, to take appropriate treatment;
(3) To realize synchronization update of cross-node table and maintain synchronization table replication;
(4) Applies to the audit, can track the data operation implemented on the table, through the record modification content and the modification person to audit the information in the table;
(5) Automatically generate the value of the derived column, realize the function of automatically obtaining the column value;
(6) Transparent recording of events, providing a transparent event log.
Implementation of 3 trigger in log management
The following implementation of the log management method, is to take advantage of the above mentioned trigger of the fourth major application, the basic idea is to log the data in the database
The table establishes a trigger that records the client's operator, Operation Object, operation time, Operation Action, and so on. As long as the in-is involved in the client program
This trigger is triggered when an operation such as SERT, update, or delete is implemented, enabling log management. This approach avoids the programmer in each of the client or intermediary server applications
Module to write a lot of repetitive program code, in the database set up a few trigger code, but also relatively simple, similar, writing, maintenance is relatively time-saving.
The following is an example of a sample user Scott's EMP table provided by Oracle9i, which enables log management of the data table by establishing a trigger, and a variety of
Operation to record. The steps are as follows:
Step One: Create the required data tables.
The first thing to do is to create two tables of Emp_log and Cur_user. Where the Emp_log table is used to record the operation log; The Cur_user table records the current user operating the client software.
It should be noted that in order to have access to the current user of the client software in a database trigger, you need to write code in the Client Software login window to record
Currently logged in to the system user, and deposited into the Cur_user table, in the trigger can be accessed by accessing the Cur_user table in the server-side database on the client's current user
Access, of course you can try other methods. The code is as follows:
--Create a user's action log table for the data sheet EMP Emp_log
CREATE TABLE Scott.emp_log (
User_name VARCHAR2 (10),
--Operator
Opera_time date,
--Operating time
operator VARCHAR2 (8),
--Operation Action
Opera_obj VARCHAR2 (20)
--Manipulating objects
);
--Create a data sheet for the current user who records the client software Cur_user
CREATE TABLE Scott.cur_user (
UserID VARCHAR2 (6),
--Operator logo
Username VARCHAR2 (10)--Current operator
);
Step Two: Create a function get_operator () for the user Scott and remove the current user from the Cur_user table that is operating the client software.
Create or Replace function Scott.get_operator
return varchar
As
V_operator Scott.cur_user.username%type;
Begin
Select username into V_operator
From Scott.cur_user;
--there is at most one record in the Cur_user table
return (V_operator);
--Returns the current user
End Get_operator;
Step three: Establish a trigger for the EMP table of the Scott user.
Create or Replace Trigger Scott.operator_emp
After insert or delete or update
On Scott.emp
For each row--row-level
Declare
V_user Scott.cur_user.username%type;
Begin
V_user:=scott.get_operator (); --Get current operator
If inserting then
--insert Operation
Insert into Scott.emp_log (user_name,opera_time,operator,opera_obj)
VALUES (v_user,sysdate, ' Insert ', ' EMP table ');
elsif updating Then
--Update operation
Insert into Scott.emp_log (user_name,opera_time,operator,opera_obj)
VALUES (v_user,sysdate, ' Update ', ' EMP table ');
elsif deleting Then
--delete operation
(next to Page 1266th)
1187
Xiayuan
Software Design and development
⑵ if the card is invalid (expired or not the parking card), the system through the visual and sound alarm, LED signs according to the cause of the error prompts the following: "Card Expired",
"Non-local card", 30 seconds after the automatic removal, the control gate is not lifted, then the security can be through the internal call system and Management Center contact, timely processing.
When the gate is lifted, the center control system automatically saves the information of the entry vehicle (entry time, card number, image, etc.), when the vehicle passes through the gate, the system starts the anti-smashing vehicle detection device to prevent
Hit the car, the vehicle through the road brake, automatic closure of the road brake.
When the vehicle arrives at the exit, the system automatically identifies the card number by reading the RFID card information. Through the internal database of information retrieval, to find the corresponding vehicle records. Interface display
The user type, license plate number, vehicle photo, and dynamically available time of entry. The camera also captures the photos of the vehicle again. The system displays the vehicle's original side
Photos and the current photo, and display the license plate number prominently so that the employee can check the number. The illegal user system will alert you.
After the temporary user pays the parking fee, the ID card is returned, and then the exit Gate is activated (3) and the user can play.
Fixed user system automatically determine the balance in the account, automatically brush off the corresponding parking fees, and then allow the appearance. If the balance is insufficient, the system reports
User can choose to renew or stop.
4 concluding remarks
The application of radio frequency identification system in non-stop toll system realizes the automatic payment, free cash, no redemption, and bank connection
Electronic, efficient and intelligent charge management, which solves the traffic jam, waste of resources, air pollution and
Money out of control and so on.
Reference documents:
[1] Li Sujian. Radio Frequency Identification Technology (RFID) theory and application [M]. Beijing: Electronic Industry Press, 2004.
[2] Chen Daxai. Radio frequency identification (RFID) technology [M]. Beijing: Electronics Industry Press, 2001.
[3] Dulili, Cao Hushan, Yanghuating. Research on access control management system based on radio frequency identification (RFID) technology [J]. Journal of Beijing University of Industry and Commerce, 2003,12.
Figure 3
Parking lot Export Process
(on page 1187th)
Insert into Scott.emp_log (user_name,opera_time,operator,opera_obj)
VALUES (v_user,sysdate, ' delete ', ' emp table ');
Else
--Other operations
Insert into Scott.emp_log (user_name,opera_time,operator,opera_obj)
VALUES (v_user,sysdate, ' Other ', ' EMP table ');
End If;
End
Description: This trigger is triggered after the client program performs an INSERT, update, or delete on the Scott.emp table, which takes advantage of the trigger's inserting, updating, and
Deleting three predicates to help determine which action is performed (insert, update, or delete).
Step four: Test the trigger in Oracle9i SQL Worksheet.
Set Serveroutput on
Declare
Cursor C_emp
Is select User_name,opera_time,operator,opera_obj from Scott.emp_log;
Begin
--Trigger Scott.operator_emp trigger (one insert, UPDATE, and two delete actions)
Insert into Scott.emp (empno,ename,job) VALUES (1209, ' gang ', ' Salesperson ');
Delete from Scott.emp
where empno=1167;
Delete from Scott.emp
where empno=1168;
Update scott.emp set job= ' sales director ' where empno=1189;
Commit
--Effective
For r_emp in C_emp loop
--Output Operation log Emp_log
Dbms_output.put_line (' User name: ' | | R_emp.user_name);
Dbms_output.put_line (' Operating time: ' | | R_emp.opera_time);
Dbms_output.put_line (' Perform operation: ' | | R_emp.operator);
Dbms_output.put_line (' Manipulating object: ' | | R_emp.opera_obj);
End Loop;
End
4 concluding remarks
This paper gives a relatively simple example of log management, the operation record is not complete. If you need a more detailed log, you can take advantage of the trigger's
(: Old and: New), record user insert (insert) records, delete older records, or modify (update) The changes in the value of some fields, this article will not repeat.