Is there a way to delete DB trigger from command line instead mark completing ? I have the below job setup and when I run it, it is creating a trigger on database and now, I need to add another LINK/TASK job to delete the DB trigger instead mark completing the job.
DB_TRIG TEST_JOB AGENT %AGENT RUN ANY USER %DB_USER DB_URL '%DB_URL' TABLE_NAME ops.ops_run_status TRIG_TYPE UPDATE TRIG_COND 'new.as_relates_to=''eis_lnp'' and +new.process_name=''db_watcher'' and new.status=''data received'''
I am not sure that I understand the question so let me know if I "Miss the boat".
You can issue a AJ command to complete the job/database trigger. If it is in the same appl you can use some variables to complete it.
ESPNOMSG AJ TEST_JOB COMPLETE APPL(%ESPAPPL..%ESPAPGEN)
In most cases I think it is best to leave it always running and control what ESP does when the trigger happens.
If you only want the trigger to work between 8am and 5pm then let the trigger happen at other times but don't build the appl in the off hours.
By forcing complete and restarting the database trigger each time it is need, it is possible that it will not get canceled in Oracle in every case. Then it is possible that there could be multiple database triggers in Oracle for the same trigger.
I do not want to complete job/APPL. I want to issue delete trigger for DB_TRIG from wob.
I can do this by right clicking on job in Workstation and select Delete Trigger option. Is it possible to do this by issuing a command also ?
Help me to understand the need here. The database trigger will continue to show active even though it is not.
Nothing will be triggering since it is not active. Later on when the wob is complete the command will fail since it can't find the trigger.
This would be like having a UNIX jobs show as running that already ended. I don't see the benefit/need. I only see confusion.
We can find the command if we need to.
Ok, let me put it in this way.
I have the DB_TRIG job which created trigger session on the database. I can delete this session from database by issuing delete trigger option when right clicking on DB_TRIG job in CA workstation.
Is it possible to delete the trigger from database in same way by issuing a wob command ?
Just so we are on the same page.
The W command can also be issued from CSF to do the same as the Workstation
Completing the job will issue the delete command to Oracle. This will keep both sides in sync.
To find the command that is issued, issue it in workstation and look at the receiver.log in the agent.