SQL QUeries 3
(A) Open and read file Notes and examples on oracle trigger.docx
(This file is used in the lecture video)
1) explain the ROLE of the following trigger, and
2) run the following code and test the trigger . Namely, insert a row in related table (show the INSERT statement) and show the content of the table before and after triggering event.
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 ;
(B) Watch the lecture video about "Trigger" and do the following tasks:
Create a trigger that when a workon record is added (namely, an employee works on a new project so an new row is INSERTED) the trigger will increase the salary of THIS employee who work on THIS project by 1% if this employees' salary is less than 85000. (for example, when your add a row (1, 100, 20) into workon table (Using INSERT statement), assuming employee 100 exists, your trigger code needs to find out (use Where clause) if the employee's salary is less than 85000, if so, increase his salary.( Hint: use After trigger, and use :new to refer the workon table after the change ) see the examples in "notes of trigger and its samples" posted at Canvas.
show me:
(1).trigger code.
(2) the table contents of employee and workon before triggering event (insert).
(3) table contents after triggering event. This way I can see if the trigger works or not. Also show your INSERT statement (insert a row into workon table to test your trigger).
Read the SQL note in for examples of triggers (link above) . Write your trigger code in notepad and paste into SQL window. If you see "trigger created " then you can test the trigger by inserting a row into workon table. Then use select * from employee to see if or not the corresponding salary of qualified employee is changed). Make sure that your trigger does not have error.
Note, if you have tried very hard and your code is still not working, do not worry, just submit your code. You may still get most of the credits for this work if the logic is correct.