Shulou(Shulou.com)06/01 Report--

What is a trigger

A database trigger is a stored PL/SQL statement associated with a table. Whenever a specific data manipulation statement (insert update delete) is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger.

The application scenarios of triggers are as follows:

Backup and audit of complex security check data confirmation database audit data II. Syntax for creating Oracle triggers CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER} {INSERT | DELETE | UPDATE [OF column [, audit...]] } [OR {INSERT | DELETE | UPDATE [OF column [, column...]] }...] ON [schema.] table_name | [schema.] view_name [REFERENCING {OLD [AS] old | NEW [AS] new | PARENT as parent}] [FOR EACH ROW] [WHEN condition] PL/SQL_BLOCK | CALL procedure_name

Where:

BEFORE and AFTER point out that the trigger timing of triggers is pre-trigger and post-trigger, respectively. The pre-trigger is to trigger the currently created trigger before the trigger event is executed, and the post-trigger is to trigger the currently created trigger after the trigger event is executed. The FOR EACH ROW option indicates that the trigger is a row trigger. The difference between row trigger and statement trigger is as follows: row trigger requires that when a DML statement manipulates multiple rows of data in the database, for each row of data, as long as they meet the trigger constraints, the trigger is activated once; while the statement trigger takes the whole statement operation as a trigger event, and activates a trigger when it meets the constraint conditions. When the FOR EACH ROW option is omitted, BEFORE and AFTER triggers are statement triggers, while INSTEAD OF triggers can only specify the relevant names for the row trigger REFERENCING clause. The relevant names can be used in the PL/SQL block and WHEN clause of row triggers to refer to the current new and old column values. The default related names are OLD and NEW, respectively. When you apply related names to the PL/SQL block of a trigger, you must precede them with a colon (:), but not in the WHEN clause. The WHEN clause describes the trigger constraint. When Condition is a logical expression, it must contain the relevant name, not the query statement, nor can it call the PL/SQL function. The trigger constraint specified by the WHEN clause can only be used in BEFORE and AFTER row triggers, not in INSTEAD OF row triggers and other types of triggers. The stored procedure to be executed when a base table is modified (INSERT, UPDATE, DELETE) is automatically triggered according to the change of the base table it is attached to, so it has nothing to do with the application. Database triggers can ensure the consistency and integrity of the data. Third, the type of Oracle trigger row-level trigger: execute once for each row affected by the DML statement, for example, a update statement updates 100 pieces of data, if we define a row-level trigger for update, then the row-level trigger will be triggered 100 times. Statement-level triggers: execute once for each DML statement, for example, a update statement updates 200 pieces of data, if we define a statement-level trigger for update, then the statement-level trigger will be triggered once. 4. Use database triggers to implement complex security checks / * implement complex security checks to prohibit the insertion of new employee weekends during non-working hours: to_char (sysdate,'day') in ('Saturday', 'Sunday') before and after work: to_number (to_char (sysdate,'hh34')) not between 9 and 18 * / create or replace trigger securityempbefore inserton empbegin if to_char (sysdate,'day') in ('Saturday') 'Sunday') or to_number (to_char (sysdate,'hh34')) not between 9 and 18 then-forbids insert operation to throw an exception raise_application_error (- 2001) 'prohibit the insertion of new employees during off-working hours') End if;end;/ in the above example, we prohibit the insertion of new employees during non-working hours, thus implementing a security check when executing insert statements.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.