We help IT Professionals succeed at work.

sybase function to oracle function

klnhk
klnhk asked
on
hi experts,

can any one help to convert the following sybase function to oracle function?
drop trigger scheduleClosingJobForUpdate
go

create trigger scheduleClosingJobForUpdate
on eipo_stock_closing_control
for update
as
begin
if update(delete_flag)
begin
delete eipo_scheduled_job
from eipo_scheduled_job, inserted
where eipo_scheduled_job.job_group_id = inserted.job_group_id
and inserted.delete_flag = 'Y'
print "--- marked deleted ---"
end
else
if update(pps_parent_id)
begin
insert into eipo_scheduled_job (sys_date, job_group_id, job_id, step_id, start_time, delay_mins, command_line, status, freq_flag, freq_intvl, prior_job1, prior_job2, prior_job3, prior_job4, parm1, parm2, parm3, parm4, parm5, parm6, parm7, parm8, parm9, parm10, instruction, create_date, create_by, last_upd_date, last_upd_by, must_complete)
select dateadd(mi, isnull(delay_mins, 0), isnull(start_time, i.close_time)), i.job_group_id, job_id, step_id, dateadd(mi, isnull(delay_mins, 0), isnull(start_time, i.close_time)), null, command_line, 'WAITING', freq_flag, freq_intvl, prior_job1, prior_job2, prior_job3, prior_job4, parm1 , rtrim(convert(char(30), i.close_time, 109)), parm3, parm4, parm5, parm6, parm7, parm8, parm9, parm10, instruction, getdate(), 'SYSTEM', getdate(), 'SYSTEM', must_complete
from eipo_batch_job, inserted i, deleted d
where eipo_batch_job.job_parent_id = i.pps_parent_id
and i.job_group_id = d.job_group_id
and i.pps_parent_id != null
and d.pps_parent_id = null
print "--- pps_parent_id is changed from NULL --> NOT null ---"

delete eipo_scheduled_job
from eipo_scheduled_job, eipo_batch_job, inserted, deleted
where eipo_scheduled_job.job_group_id = deleted.job_group_id
and eipo_scheduled_job.job_id = eipo_batch_job.job_id
and eipo_batch_job.job_parent_id = deleted.pps_parent_id
and inserted.job_group_id = deleted.job_group_id
and inserted.pps_parent_id = null
and deleted.pps_parent_id != null
print "--- pps_parent_id is changed from NOT null --> NULL ---"

end

if (@@error !=0)
rollback tran
print "-- update trigger on eipo_stock_closing_control: fired --"
end
go
Comment
Watch Question

Commented:
klnhk,

What version of Oracle are you running?  This can be converted but can you provide a bit more information?  While Sybase basically has 3 triggers, Oracle has like 15 or so.  Basically, they are the same its just preinsert and post insert stuff version and a few oracle specific ones.  If you arent sure, then let me know. I can get you a preinsert version.

knel
Your history reflects that you have asked a total of 38 questions at this site and only finalized 10 of them.  I think you'll agree this is not fair to the experts who have stepped in to help you, and it is against our Guidelines and Member Agreement, listed on the left under Help Desk.

I will update all your open items with a request to finalize them so that you are advised by Email of their open status and can quickly navigate through them and complete them.

If you need help to split points, process a refund and move to PAQ at zero, or otherwise special handle this question, please let us know.  I will monitor them all, and as usual, appreciate any expert input here.

Please also refer to these links:
http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp
http://www.experts-exchange.com/jsp/infoMemberAgreement.jsp

Thanks,

Moondancer
Community Support Moderator @ Experts Exchange
klnhk logged in after follow up requests posted.  These 50 points have been refunded and this item closed.  Moved to our PAQ at zero points, so that all are advised of outcome.
Moondancer
Community Support Moderator @ Experts Exchange

Explore More ContentExplore courses, solutions, and other research materials related to this topic.