Triggers using tutorials and naming conventions

   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       &NBSP;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</