Relational DBMS (OLD)-Triggers

 CBSE Class 12 Informatics Practices

Revision Notes
CHAPTER -15
TRIGGERS

---

What is a Trigger:
A trigger is a stored procedure that defines an action the database automatically initiates when some database related events such as INSERT, UPDATE OR DELETE occurs. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. For Example when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.

Why Triggers:
A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. Basically it is a predefined value which is invoked when its needed according to the programmer. Triggers are commonly used to : 1. Automatically generate derived column values.
2. Prevent invalid transactions (Invalid Values)
3. Provide transparent event logging
4. Maintain synchronous table replicates
5. Gather statistics on table access

Triggers Vs Procedures:
1. Triggers Execute Implicitly while Procedure execute explicitly.
2. Triggers do not accept arguments while Procedures may or may not have arguments.
3. Triggers are fired for DML(Insert, Update or Delete) statements & DDL(SQL CREATE, ALTER, and DROP statements) while procedures execute all DML statements including SELECT.

Trigger Vs constraints: Constraints are used to maintain the integrity and atomicity of database. The main 5 constraints are NOT NULL,PRIMARY KEY,FOREIGN KEY,UNIQUE KEY and CHECK.
1. Triggers affect only the row added after the trigger is enabled.
2. Affects all the rows in a table
Syntax :
CREATE [OR REPLACE] TRIGGER trigger_name
Before/after insert/update/ delete
[ofcolumnname]
ON table_name
[For each Row]
BEGIN
SQL statements;
END [triggername];

The structure of a row-level
CREATE OR REPLACE TRIGGER ***trigger name***
***when***
ON
***which table***
FOR EACH ROW
***conditions for firing***
begin
***stuff to do***
end;

Components of a trigger definition:
1. Trigger Name- This clause creates a trigger with the given name or overwrites an existing trigger with the same name. CREATE [or REPLACE] TRIGGER <trigger name>
2. Trigger time point - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. before | after
3. Triggering Event(s)- This clause determines the triggering event. insert or update [of <column(s)>] or delete on <table>
4. Trigger type (optional)- This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger). for each row
5. Trigger restriction - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified. when (<condition>)

Executing Triggers: When using SQL*Plus, you have to provide a / character to get the program to evaluate a trigger or PL/SQL function definition. You then have to say "show errors" if you want SQL*Plus to print out what went wrong. Unless you expect to write perfect code all the time, it can be convenient to leave these SQL*Plus incantations in your .sql files.

Types of Triggers
1. Row level triggers
2. Statement Level Trigger
3. Before and after Trigger
4. Instead of Trigger
5. Trigger on system events and user events

Example: 1
SQL> Create or replace trigger empcount
After insert on emp
For each row
Declare
n integer;
Begin
Select count(*) into n from emp;
dbms_output.put_line(‘total no. of records in a table is : ‘ | |n);
End;

Accessing coloumn values:
¦ :old .<colomn name>
¦ :new. <Colomn name>

* * Points to Remember * *
* Only with a row trigger it is possible to access the attribute values of a tuple before and after the modification (because the trigger is executed once for each row).
* For an update trigger, the old attribute value can be accessed using :old.<column> and the new attribute value can be accessed using :new. <column>.
* For an insert trigger, only :new. <column> can be Used.
* for a delete trigger only :old.<column> can be used (because there exists no old, respectively, new value of the tuple). In these cases, :new.<column> refers to the attribute value of <column> of the inserted tuple, and :old.<column> refers to the attribute value of
<column> of the deleted tuple.
In a row trigger thus it is possible to specify comparisons between old and new attribute values in the PL/SQL block,
e.g., “if :old.SAL < :new.SAL then . . . ”.
If for a row trigger the trigger time point before is specified, it is even possible to modify the new values of the row, e.g., :new.SAL := :new.SAL * 1.05 or :new.SAL := :old.SAL.
Such modifications are not possible with after row triggers.

Example: Create the 'product' table and 'product_price_history' table

CREATE TABLE product_price_history
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) ); 

CREATE TABLE product

(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32), 
unit_price number(7,2) ); 

Now we Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger 
BEFORE UPDATE OF unit_price 
ON product 
FOR EACH ROW 
BEGIN 
INSERT INTO product_price_history 
VALUES 
(:old.product_id, 
 :old.product_name, 
 :old.supplier_name, 
 :old.unit_price); 
END; 
/ 

Example: 2
SQL> Create or replace trigger EMPUPD
Before update on emp For each row
Begin
if :new.salary<:old.salary then
Dbms_output.put_line(‘Salary can not be reduced’);
End

Example: 3
statement level trigger- SQL> Create or replace trigger EMPUPD Before update on emp
Begin
if :new.salary<:old.salary then
Dbms_output.put_line(‘Salary can not be reduced’);
End;

Example 4:
SQL> Create or replace trigger EMPUPD
After update on
emp n number;
Begin
select count(*) into n from emp;
Dbms_output.put_line(‘Total Records in table EMP : ’ | |n);
End;

¦ Enabling a Trigger is:
ALTER TRIGGER trigger_name ENABLE;

For example:
If you had a trigger called orders_before_insert, you could enable it with the
following command:
ALTER TRIGGER orders_before_insert ENABLE;

Disable a Trigger
syntax:
ALTER TRIGGER trigger_name DISABLE;

For example:
ALTER TRIGGER orders_before_insert DISABLE;

Drop a Trigger
syntax:
DROP TRIGGER trigger_name;

For example:
DROP TRIGGER orders_before_insert;

Example:
create or replace trigger check_budget_EMP
after insert or update of SAL, DEPTNO on EMP
declare
cursor DEPT_CUR is select DEPTNO, BUDGET from DEPT; DNO DEPT.DEPTNO%TYPE;
ALLSAL DEPT.BUDGET%TYPE;
DEPT_SAL number;
begin
open DEPT_CUR;

loop
fetch DEPT_CUR into DNO, ALLSAL;
exit when DEPT_CUR%NOTFOUND;
select sum(SAL) into DEPT_SAL from EMP where DEPTNO = DNO;
if DEPT_SAL > ALLSAL then
raise_application_error(-20325, 'Total of salaries in the department '|| to_char(DNO) || ' exceeds budget');
end if;
end loop;
close DEPT_CUR; end; /

More about triggers : Triggers are not exclusively used for integrity maintenance. They can also be used for
• Monitoring purposes, such as the monitoring of user accesses and modifications on certain sensitive tables.
• Logging actions, e.g., on tables:
Contd..
create trigger LOG EMP
after insert or update or delete on EMP
begin
if inserting then
insert into EMP LOG values(user, ’INSERT’, sysdate);
end if;
if updating then
insert into EMP LOG values(user, ’UPDATE’, sysdate);
end if;
if deleting then
insert into EMP LOG values(user, ’DELETE’, sysdate);
end if;
end;
By using a row trigger, even the attribute values of the modified tuples can be stored in the table EMP LOG.
• automatic propagation of modifications. For example, if a manager is transferred to another department, a trigger can be defined that automatically transfers the manager’s employees to the new department.

More about Triggers
If a trigger is specified within the SQL [1] Plus shell, the definition must end with a point “.” in the last line. Issuing the command run causes SQL*Plus to compile this trigger definition.
A trigger definition can be loaded from a file using the command @. Note that the last line in the file must consist of a slash “/”.
A trigger definition cannot be changed, it can only be re-created using the or replace clause.
The command drop <trigger name> deletes a trigger.
After a trigger definition has been successfully compiled, the trigger automatically is enabled.
The command alter trigger <trigger name> disable is used to deactivate a trigger. All triggers defined on a table can be (de)activated using the command alter table <Tablename> enable | disable all trigger;
The data dictionary stores information about triggers in the table USER TRIGGERS. The information includes the trigger name, type, table, and the code for the PL/SQL block.

Difference b/w For and Do Loops: When No. of repetitions are known then For loop is used, and if the No. of iterations are unknown then do loops are used.

Difference b/w While and Until: While means as long as the condition is true, the loop execute the body Whereas Until means as long as the condition is not true, the loop repeats Exiting from Loop: Exit statement helps to terminate any of the loops directly.
EXIT DO: To terminate any Do loop
EXIT FOR: To terminate for loop

Use of For Each ... Next Loop: It is used to repeat a group of statements for each element in a dynamic array as we are not sure about the size of the array.

Two Basic Operations on Arrays: Traversing means processing each element of the array Searching means to find a given element in array
Calling Procedure: It’s a procedure that calls another procedure.
Called/Caller Procedure: The procedure being called is known as Called / Caller Procedure.
Actual Parameters: The parameters provided by calling procedures are actual.
Formal Parameters: The parameters received by called procedures are formal.

A sub procedure may call in two ways:
With a call statement - Call procedure-name (actual arguments list) Eg: Call abc (x, y)
Without call statement - procedure - name actual arguments Eg: abc x, y
If Private/Public keyword is not specified with a procedure then the procedure becomes Public.
The value being returned by the function is assigned to the function name, which automatically returns it to the calling procedure or function. A function may return only one value.

*Sub procedure does not return a value, so a call to a sub procedure is a complete statement.
* Function procedure returns a value, so a call to a function procedure is part of an expression.
In a procedure, optional parameters are declared in argument list from right hand side.
Sub OptProcedure( ByVal X as Integer, ByVal Y as Integer, ByVal Optional Z as Integer)
VB Passes an argumentby Reference by default.
Exit suband Exit Function statements can be used to Exit from a sub procedureor a function procedure.
If a variable is declared as PUBLIC A as Integerin form1and it’s value is 20, then it can be used in form2 as form1.A.

List the variable scopes in decreasing lifespan: PUBLIC, MODULE, STATIC, LOCAL
Try this:
Sub MyProc1 ( )
Dim A as Integer
A = 12
Print A
Call MyProc2 (A)
A = A + 2
Print A End Sub
Sub MyProc2 (B as Integer)
Print B
B = B + 10
Print B
End Sub

O/P is:
12
12
22
24

When a number is converted to a string, a leading space is always reserved for its sign.
St = Str (198) ‘ Gives “ 198”
St = Str (-198) ‘ Gives “-198”
Cint () function returns truly rounded number. Eg. : Print CInt (-14.8) will print -15.