Triggers for Oracle objects

Triggera database trigger is a table-associated, storedPL/SQLprogram. Whenever a specific data manipulation statement(Insert,update,delete)when emitted on a specified table, theOracleautomatically executes the sequence of statements defined in the trigger. 1.Trigger Action

• Data Validation  
• Implement a complex security check
• Doing audits, tracking data operations on the table, etc.
• Backup and synchronization of data

2.types of triggersstatement-level triggers : Executes one time before or after the specified action statement operation, regardless of whether the statement affectsthe number of lines . row-level triggers (For each ROW) : Each record that triggers the action of the statement is triggered. Row-level Touchused in the hair generator Oldand theNewpseudo-Record variables, identifies the status of the value.

Grammar:

CREATE [or REPLACE] TRIGGER Trigger Name

{before | After}

{DELETE | INSERT | UPDATE [ of column name ]}

on Table name

[For each ROW [when ( condition )]]

Declare

......

Begin

Plsql Block End trigger name;

1 Example: Inserting an employee after printing a word "a new employee inserted successfully"2  3 Create or replace trigger Testtrigger4  5 After insert on person6  7 Declare8  9--Local Variables hereTen   One begin A   -Dbms_output.put_line (' An employee was inserted '); -   theEnd Testtrigger;

1 Example: Cannot insert employee during break time2  3 Create or replace trigger Validinsertperson4  5 before insert on person6  7 Declare8  9Weekend VARCHAR2 (10);Ten   One begin A   -Select To_char (sysdate, ' Day ') into weekend from dual; -   the   ifWeekend in (' Monday ') Then -   -Raise_application_error (-20001, ' cannot insert employee at illegal time ');//throws an acceptable exception to the -   +Endif; -   +End Validinsertperson;

Error when inserting is performed

Triggering the values of statements and pseudo-record variables in triggers

Trigger statement	: Old	: New
Insert	all fields are empty (NULL)	The data that will be inserted
Update	Update the previous value of the row	The updated value
Delete	Delete the previous value of the row	all fields are empty (NULL)

1 Example: Judging the employee's salary after the wage increase must be greater than the wage before the increase in wages2  3 Create or replace trigger addsal4p4  5 before update of Sal on Myemp6  7    forEach row8  9 beginTen   One   if: Old.sal >=:New. Sal Then A   -Raise_application_error (-20002, ' the wage before the rise cannot be greater than the wage after the rise. ')); -   theEndif; -   -End

Call

Update myemp T Set t.sal = t.sal- 1 ;

Triggers for Oracle objects