How to write trigger code on emp?

by Raja
(Hyderabad)

I have one requirement.

In emp table i have added direct_ind. before 5 clock i want to update this direct_ind = 'Y' and after 8 clock i want to change direct_ind = 'N'. i will execute update statement 8 clock direct_ind 'Y' to 'N' . if direct_ind = 'y' then user should not modify and delete records how to write this code in trigger

Thanks
Raja

It seems to us that you actually have 3 requirements (not just the one) and only one of which could be net by using a trigger and even that may be done better by other methods but we'll get to that.

Here are your requirements as we see them:
  1. before 5 o'clock you want to update emp and set direct_ind to 'Y'
  2. after 8 clock you want to update emp and set direct_ind = 'N'
  3. when direct_ind='Y' the row cannot be modified or deleted

To meet your first 2 requirements you should use dbms_scheduler to schedule a PL/SQL program to update the emp table. DBMS_SCHEDULER is a very flexible built-in PL/SQL package that accepts different sets of parameters depending on your requirements. The details can be found here but essentially you give it a job name, the name of the program you want to run and when you want it to run. You can't use dbms_scheduler to run SLQ scripts but remember you can embed SQL statements directly into your PL/SQL programs so this is not an issue.

That takes care of the first 2 requirements, as for the 3rd, that's not so straightforward. The rquirement as we understand it is to prevent modification to any row in emp where direct_ind='Y'. The only way to do this with a trigger would be to have the trigger raise an exception which would automatically cause the current transaction to be aborted. However unless the exception is caught (handled) by the calling program then execution of the calling program would be aborted automatically by Oracle. Therefore it is not a very elegant way of approaching this.

A better way to do this would be to have the application handle this condition. That way you could also pass back a meaningful error message to the user (assuming it's an interactive application) and/or log an error message to a log table.

It is possible to have your trigger call DBMS_OUTPUT.PUT_LINE and output some text but you would then have to modify your application to get this output so it is not practical really.

This is how your trigger could look if you did decide to implement this logic in a trigger.

CREATE OR REPLACE TRIGGER protect_emp
BEFORE UPDATE OR DELETE ON emp FOR EACH ROW
BEGIN

  IF :old.direct_ind='Y' THEN
    dbms_output.put_line ('emp id '||:old.emp_id||' cannot be modified');
       RAISE NO_DATA_FOUND;
END IF;

END;


The following questions and answers on triggers may also be useful
Also see our SQL tutorials and our PL/SQL tutorials for more help.

Click here for information about our classroom quality Oracle training courses.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.