How to write trigger code on emp?
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
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:
- before 5 o'clock you want to update emp and set direct_ind to 'Y'
- after 8 clock you want to update emp and set direct_ind = 'N'
- 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
IF :old.direct_ind='Y' THEN
dbms_output.put_line ('emp id '||:old.emp_id||' cannot be modified');
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