A trigger is an SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the Oracle database, and are used to maintain the referential integrity of data by changing the data systematically. A trigger cannot be called or executed. Triggers are fired automatically by Oracle.
The database triggers are used for the following purpose:
1. To generate the resulting data automatically.
2. To enforce complex integrity constraints, security authorizations, and business rules.
3. To avoid invalid transactions.
4. To enforce referential integrity across nodes in a distributed database.
5. To provide transparent event logging and advanced auditing data modifications.
6. To maintain synchronous table replicates and check the status of the table access.
Example: Write a PL/SQL block that prompts a message to enter an employee id and also prompts a message that the employee is present.
The following steps are required to illustrate the use of attribute %TYPE with the implicit
cursor:
- Open an editor (Notepad), enter the following code in it, and then save the file at C:\oracle_11g\c08_oracle_11g with the file name employee_Cur_Type.txt. Alternatively, enterthe following code directly in SQL * Plus.
- DECLARE
Name Emp.Ename%TYPE;
Salary Emp.Sal%TYPE;
EmpId Emp.Empno%TYPE;
BEGIN
EmpId := &EmpId;
SELECT Ename, Sal INTO Name, Salary
FROM Emp
WHERE Empno = EmpId;
DBMS_OUTPUT.PUT_LINE (Name || ‘ having Employee id ’ || EmpId ||
‘ and salary ’ || Salary || ‘ is present’);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (‘The employee is not in the company
or does not exist’);
END;
/
In the above PL/SQL block, Name is declared as the %TYPE attribute against the column Ename of the Emp table and Salary is declared as the %TYPE attribute against the column Sal of the Emp table. In the BEGIN section, the statement EmpId := &Empid; will prompt a message Enter value for empid and the value entered by you will be stored in the variable EmpId. The PL/SQL will execute the SELECT statement and retrieve a message that the employee having the employee number you entered is present. If the employee id you entered does not exist, Oracle will raise an exception NO_DATA_FOUND and prompt the message Employee does not exist.
- Now, execute the contents of the file employee_Cur_Type.txt by entering the followingcommand in SQL *Plus:
SQL>@‘C:\oracle_11g\c08_oracle_11g\employee_ImpCur.txt’
3. When you execute the above PL/SQL block, the message Enter value for empid will be
prompted. Enter a value which exists in the Emp table such as 7900; a message will be
printed as given below:
Enter value for empid: 7900
old 6: EmpId := &EmpId;
new 6: EmpId := 7900;
JAMES having Employee id 7900 and salary 950 is present
PL/SQL procedure successfully completed.
4. Again, execute the same PL/SQL block by entering the forward slash (/) at the SQL
prompt. The message Enter value for empid will be prompted. This time enter the value of employee id that does not exist in the Emp table such as 234; the exception
NO_DATA_FOUND will be raised and a message will be printed as follows:
SQL> /
Enter value for empid: 234
old 5: EmpId := ‘&EmpId’;
new 5: EmpId := ‘234’;
The employee is not in the company or does not exist
PL/SQL procedure successfully completed.
