record trigger usage tutorials and Naming Conventions 1 1, Introduction to triggers 1 2, examples of triggers 2 3, trigger syntax and function 3 4, example one: row-level triggers 4 5, example two: row-level triggers 4 6, example three: Instead of triggers 6 7, example four: statement-level triggers one 8 8, example five: statement-level triggers bis 9 9, example six: Package-encapsulated trigger code 10 10, trigger naming convention 11 1, Introduction to triggers triggers (Trigger) are a kind of database objects, encoded like stored procedures, Associated with a table, when a DML statement operates on a table, triggers are executed to achieve consistency, correctness, and normative control of the insertion record. When the C/S era prevailing, because the client directly connected to the database, to ensure that the database consistency is only the database itself, at this time the primary key (Primary key), foreign Key (Foreign key), Constraints (Constraint) and triggers become the necessary control mechanisms. The realization of the trigger is more flexible and programmable, and naturally becomes the most popular control mechanism. To the B/s era, developed into a 4-tier architecture, the client can no longer directly access the database, only middleware can access the database. To control the consistency of the database, it can be controlled either in the middleware or at the database end. Many Java developers, with the database as a black box, the majority of data control work in the servlet execution. This does not require much knowledge of the database and reduces the complexity of database programming, but at the same time increases the amount of servlet programming. From the perspective of architecture design, the function of middleware is to check business correctness and execute business logic, if the consistency check of data is put into middleware, it needs to be involvedand data consistency checks where data is written. Since database access is a remote invocation relative to the middleware, it is not easy to write uniform data consistency checking code, which is generally used to add similar checking steps in multiple places. Once the consistency check process has been adjusted, it is bound to result in a number of local changes, not only increase the workload, but also can not guarantee the correctness of each check step. The application of the trigger should be placed on the key, multi-party, high-frequency Access data table, excessive use of triggers, will increase the burden of the database, reduce database performance. Abandoning the use of triggers will cause problems in the system architecture design and affect the stability of the system. 2, trigger example trigger code similar to stored procedure, Pl/sql scripting. The following is an example of a trigger: new Employee Payroll SALARY create table salary ( employee_id number, --Staff id MONTH  VARCHAR2 (6), --salary month amount number -- Wage amount ) create triggers associated with salary salary_trg_rai 1 create or replace trigger salary_trg_rai 2 after insert on salary 3 for each row 4 declare 5 begin 6 dbms_output.put_line (' Employee ID: ' | | :
New. employee_id); 7 Dbms_output.put_line (' Salary month: ' | |:
New. month); 8 Dbms_output.put_line (' Wages: ' | |:
New. amount); 9 Dbms_output.put_line (' Trigger has been executed '); Ten end; Open a SQL window window (using the Pl/sql Developer tool), or enter in Sqlplus: Insert into salary (employee_id, month, amount) VALUES (1, ' 200606 ', 10000); After execution you can see employee id:1 in Sqlplus, or in output in the SQL window window: 200606 Salary: 10000 The trigger has been executed in the first line of code, defining the type of database object is trigger, defining the trigger The name is Salary_trg_rai the second line shows that this is an after trigger that executes after the DML operation has been implemented. The following insert shows that this is a trigger for the insert operation, and each insert operation on the table executes the trigger. The third line shows that this is a row-level trigger, and that when the inserted record has n bars, the trigger is executed every time the insert operation is executed, in total n times. Declare is followed by the local variable definition section, which, if there is no local variable definition, can be the code that encloses the null begin and end, is the execution part of the trigger, and generally checks the insertion record for consistency, in this case the inserted record is printed and the trigger is executed. which
Newobject represents the inserted record, which you can use by:
New. column_name to reference each field value of the record 3, trigger syntax and functionality trigger syntax as follows CREATE OR REPLACE TRIGGER trigger_name <before | after | instead of> <insert | update | delete> on table_name [for each row] when (condition) DECLARE BEGIN --trigger code end; Trigger_name is the name of the trigger. <before | after | instead of> can choose before or after or instead of. Before represents the execution of a trigger before a DML statement is implemented, and the after representation executes the trigger after the DML statement is implemented, and the Instead of trigger is used on the update of the view. <insert | update | delete> You can select one or more DML statements, and if you select multiple, separate them with or, such as: Insert or update. TABLE_NAME is the name of the table associated with the trigger. [For each row] is optional and if For each row is indicated, the trigger is a row-level trigger, and the DML statement processes each record to execute the trigger ; otherwise, a statement-level trigger that fires once for each DML statement. when followed by conditionIs the response condition of the trigger, valid only for row-level triggers, which are executed if the record of the operation satisfies condition, otherwise the trigger is not executed. In condition, you can pass
NewObjects and old objects (note different from the previous:
NewAnd: old, referencing the record of the operation by referring to a colon in code. Trigger code can include three types: No database transaction code involved, related tables (table_name in the syntax above) database transaction code, involving the database transaction code in addition to the associated table. The first type of code only performs simple operations and judgments on data, without DML statements, which can be executed in all triggers. The second type of code involves data manipulation of the associated table, such as querying the total number of records in the associated table or inserting a record into the associated table, which can only be used in statement-level triggers and will report ORA-04091 errors if used in row-level triggers. The third type of code involves a database transaction other than the associated table, which can be used in all triggers. From the point of view of the function of the trigger, can be divided into 3 categories: Rewrite columns (for before triggers only) take action (any trigger) to reject a transaction (any trigger) Override column is used to validate a table field, when the insertion value is empty or the insertion value does not meet the requirement, the trigger defaults to the default value or Other values instead, in most cases with the field's
defaultproperty is the same. This feature can only be performed in a row-level before trigger. "Take action" for the characteristics of the current transaction, the related table operations, such as the record inserted according to the current table to update other tables, the bank's general ledger and the total score between the accounts can be maintained through this trigger function. A denial of transaction is used to validate the legality of the data, and when the updated data does not meet the consistency requirements of the table or system, the transaction is rejected by throwing an exception, and the code at its top can catch the exception and do the appropriate action. The following example illustrates the syntax of the trigger body in an example, and the reader can appreciate the function of the trigger in the example. 4, example one: one of the row-level triggers create or replace trigger salary_raiu after insert or update of amount on salary FOR each row BEGIN IF inserting THEN dbms_output.put_line (' Insert '); ELSIF updating THEN dbms_output.put_line (' Update Amount column '); END IF; end; above is a row-level trigger for After insert and after update. Of amount on salary in the second row means that the update trigger will be valid only if the amount column is updated. Therefore, the following statement will not execute the trigger: update salary set month = ' 200601 ' where month = ' 200606 '; In the trigger body's
If Statement expressions, inserting, updating and deleting can be used to distinguish which DML operations are currently being made, and can be used as a property to identify trigger events by merging multiple similar triggers in a single trigger. 5, example two: row-level Triggers II new Employee table Employment create table employment ( employee_id number, --staff id maxsalary number --salary cap ) insert two records insert into Employment values (1, 1000); insert into employment values (2, 2000); create or replace trigger salary_raiu After insert OR UPDATE OF amount ON salary for each row when ( NEW.amount >= 1000 AND (old.amount is null or old.amount <= 500)) DECLARE v_maxsalary number ; BEGIN SELECT maxsalary into v_maxsalary FROM employment WHERE employee_id = :NEW.employee_id; IF :NEW.amount > v_maxsalary THEN raise_application_error ( -20000, ' wage overrun '); end if ; end; The example above introduces a new table employment, in which the Maxsalary field represents the maximum salary that the employee can assign each month. The following triggers find the employee's maximum monthly salary in the employment table based on the employee_id of the inserted or modified record, and report an error if the inserted or modified amount exceeds this value. The When clause in the code indicates that the trigger is only more than 1000 for the amount value modified or inserted, and that the amount value before the modification is less than 500 of the record. The new object and the old object represent the record objects before and after the operation. For insert operations, because the current action record has no history objects, all properties in the old object are
null</
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.