SQL QUeries 3

· Notes and examples on oracle trigger

Create trigger A trigger is a stored program that is attached to a table or view. It is used to maintain the data integrity in database system. The trigger code is invoked by DBMS when an insert, update or delete request is issued on the table to which a trigger is defined.

A row level trigger is in the format 1) Create trigger trigger_name Before/after triggering_event on table_name For each row Begin Any SQL or PL/SQL statements End A triggers is stored and called/fired implicitly when a triggering event (insert, update or delete) occurs. A trigger can be designed to fire before or after the triggering event. e.g. (when delete a course, a trigger will delete all its enrollment records) 2)

create or replace trigger employee_bd_trigger before delete on employee for each row begin delete workon where empid = :old.empid; end; Type in 'show error' to display the complilation error. Pay attention to the condition in delete statement. In each triggering event, Oracle create two virtual tables to keep the ‘before’ and ’a fter’ image of the table being changed. :old refers to the before image, :new refers to the after image. You can refer both in a trigger. Using above code as an example, you can do the assignment.

 

More examples:

/create a trigger that increments the project count for an employee where time the employee has a new workon record added.

3)

create or replace trigger workon_ai_trigger after insert on workon for each row begin update employee

set totalprojects = totalprojects + 1 where employee.empid = :new.empid; end ;

4) /* for delete , ...*/ create or replace trigger workon_ad_trigger after delete on workon for each row begin update employee

set totalprojects = totalprojects - 1 where employee.empid = :old.empid; end